DateSerial formula help

[Chad]

Hello, I cant figure this out! I want to use the DateSerial to find these
date ranges. I searched all over the net/forums and really didnt get an
answer. I need to find the first and last days of the previous year and I
also need to find the first day (Sunday) and last day (saturday) of the
previous week.

I had found this Ms site (Very Helpfull) but nothing on years...
http://support.microsoft.com/kb/210604

 

[Douglas J. Steele]

Since every year starts on January 1, and ends on December 31, the first day
of the previous year is

DateSerial(Year(Date) - 1, 1, 1)

and the last day of the previous year is

DateSerial(Year(Date) - 1, 12, 31)

For the second part of your question, you can use the Weekday function to
determine the week day of the current day, and then subtract the appropriate
number of days from the current day once you know what weekday it is. For
instance, today (02 Mar, 2008) is a Sunday (Weekday(Date) = 1). That means
to determine the Sunday of the previous week, you'd need to subtract 7 from
today. Similarly, tomorrow (03 Mar, 2008) will be Monday, so you'd need to
subtract 8 days to get the Sunday of the previous week. You can use the
DateSerial function:

DateSerial(Year(Date), Month(Date), Day(Date) - 6 - Weekday(Date))

but it's actually better to use the DateAdd function:

DateAdd("d", -6 - Weekday(Date), Date)

Hopefully it's apparent that the previous Saturday can be calculated as

DateAdd("d", -Weekday(Date), Date)

 

[Chad]

Thank you so much! Do you have a good reading resources on finding date
ranges using the DateSerial and DateAdd? Thanks again!

 

[Douglas J. Steele]

I'm not aware of any resource, but both functions are fairly
straight-forward.

DateAdd adds (or subtracts) a specific time interval to a given date.

DateSerial requires three arguments: year, month and day. When any argument
exceeds the accepted range for that argument, it increments to the next
larger unit as appropriate. For example, if you specify 35 days, it is
evaluated as one month and some number of days, depending on where in the
year it is applied. If any single argument is outside the range -32,768 to
32,767, an error occurs. If the date specified by the three arguments falls
outside the acceptable range of dates, an error occurs.

 

[Jackie L]

Chad,
This article is great for displaying specific dates:
http://support.microsoft.com/kb/210604