finding the last day of the previous month 1

[smiley0q0]

i have fields that contain one date of every month for the past year, they are all different. i need to find the last day of the previous month for each of those fields. i won't know what the date is in the date field, it varies everyday. how would i do this?

thanks in advance :-9

 

[dneufarth]

DateSerial(<year>, <month>, 0)



Dave

 

[smiley0q0]

i don't quite understand... cannot you elaborate a little.

the date fields are dynamic, the formula i use to bring up the date for 12 months ago is:
=DateAdd(&quot;d&quot;,IIf(Format((DateAdd(&quot;m&quot;,-12,Date())-1),&quot;ddd&quot=&quot;Sat&quot;,-1,IIf(Format((DateAdd(&quot;m&quot;,-12,Date())-1),&quot;ddd&quot=&quot;Sun&quot;,-2,0)),DateAdd(&quot;m&quot;,-12,Date())-1)

for this result i need to find the last day of the month 13 months ago.

 

[dneufarth]

If you want to know the last date of the prior month based on today, the DateSerial function will provide it.

Example: DateSerial(Year(Date()), Month(Date()),0)

If Date() returns 3/23/2001 then above would return 2/28/200.


In your case you can substitute DateAdd(&quot;m&quot;,-12,Date())-1) for Date() as in

DateSerial(Year(DateAdd(&quot;m&quot;,-12,Date())-1), Month(DateAdd(&quot;m&quot;,-12,Date())-1),0)


Dave

 

[smiley0q0]

AWESOME!!!!! THANK YOU!! works like a charm :-V :-D

 

[MichaelRed]

Actually, neufarth's first answer is easier, if you just follow the logic one more step.

MyDate: DateSerial(Year([DateField]), Month([DateField], 0)

returns the last day of the previous month for any Date in [DateField].

like Mikey says, &quot;Try it, You'll Like It&quot;





MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over