Date Serial for 12 months ago? (1 Viewer)

[gojets1721]

I was hoping to use a dateserial function to produce a date that is 12 months prior to today's date.

So if today is 11/10/2022, then the date serial function would show 11/11/2021. Basically exactly 365 days prior.

Any suggestions?

 

[plog]

Basically exactly 365 days prior.

Basically exactly? That's some fine wordsmithing.

You would literally figuratively use DateAdd, not DateSerial:

MS Access: DateAdd Function

This MSAccess tutorial explains how to use the Access DateAdd function with syntax and examples. The Microsoft Access DateAdd function returns a date after which a certain time/date interval has been added.

www.techonthenet.com

Be careful when you cross over leap days--what basically exactly do you want for 12 months/365 days prior to 3/3/2024? Pretty sure -12 months produces a different result than -365 days.

 

[MajP]

Datesserial(year(date),month(date),day(date)-1)

 

[MajP]

FYI. The date serial is really smart. If the day is the first of the month and you subtract 1 you are providing 0 as the day. It will convert to last day of previous month. Same if you provided a day like 32.

 

[gojets1721]

FYI. The date serial is really smart. If the day is the first of the month and you subtract 1 you are providing 0 as the day. It will convert to last day of previous month. Same if you provided a day like 32.

Perfect. Thank you!!

 

[gojets1721]

Dateserial(year(date),month(date),day(date)-1)

So this produced yesterday's date (11/9/2022). I'd be looking for it to produce 11/11/2021

 

[Minty]

Well, I would possibly subtract 1 from the year rather than the day section of the function?

Try it and see what happens. It won't bite you.

 

[MajP]

Sorry typed on a phone. Subtract one from year, year(date) -1 and add a day not subtract.

 

[MajP]

Point is you want it to be a day before in the previous year. Not always 365.
Unless you do want 365 regardless of leap year. Need to be specific which way.

 

[gojets1721]

Sorry typed on a phone. Subtract one from year, year(date) -1 and add a day not subtract.

This worked. Thanks so much!