last date of previous month 2

[ali32j]

Hi All

Last question i promise, can anyone tell me how i can enter the last date of the last full month dependant on todays date. ie

using NOW() function, enters the last day of the last full month

Ali

 

[SkipVought]

hi,

msgbox dateserial(year(date),month(date),0)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ali32j]

Hi Skip

I m using Excel, not sure how to use this statement...

Is this not a pop up box? I m looking for excel to look at the date in a cell with =NOW() in it and autopopulate another cell with the last date of the previous full month, so in terms of todays date it would enter 31/10/2009 in every cell in column I in my table


Ali

 

[SkipVought]

You posted in VBA.

If you want this on the sheet forum68...
[tt]
=date(year(today()),month(today()),0)
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[CoSpringsGuy]

=DATE(YEAR(B3),MONTH(B3),1)-1

B3 being the cell with the now()

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[SkipVought]

CoSpringsGuy,

Actually...
[tt]
=DATE(YEAR($B$3),MONTH($B$3),0)
[/tt]
since this formula will be copied to many cells, the reference to todays's data must be ABSOLUTE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DaveInIowa]

That's an interesting use of the Day parameter Skip. Learned something new. Thanks *

 

[CoSpringsGuy]

got it! Thanks.... I was responding to the formula not so much the usage but you are ABSOLUTEly correct and I should have addressed that

I also learned something .. didnt know you could use 0 for the day. Other languages give you an error doing that which is why I subtracted 1 from the entire formula.

Have a Happy Thanksgiving!

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[PHV]

Another way:
=TODAY()-DAY(TODAY())

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

PHV, even MORE elegant!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]