Date Calculation

[Leesha]

Hi,

I need to find two dates. I need the first and last day of the year for the previous year that the user is running the report. So if they are running the report in 2013, the dates returned would be 1/1/2012 and 12/31/2012. I tried =DateSerial(Year(Date()),-1,1) and =DateSerial(Year(Date()),1,-1) but neither worked.

The other date that I need is the week ending date for the pervious week. The formula that I'm using to obtain the curren week ending date is WEDate: Date()+7-Weekday(Date(),7). This work fine. I tried
WEDate: Date()-7-Weekday(Date(),7) but it gives me the weekending date two weeks back. I tried WEDate: Date()-0-Weekday(Date(),7) and that gave me the right date however I want to be sure I'm not missing anything.
Thanks!
Leesha

[HansV]

First day of previous year: =DateSerial(Year(Date())-1,1,1)
Last day of previous year: =DateSerial(Year(Date()),1,0)

For the end-of-week date: you can even omit the -0, that doesn't do anything: =Date()-Weekday(Date(),7)

[Leesha]

Thanks Hans! So that I "understand it" fon the seria date forumula, wat exactly do the numbers represent??

Leesha

[HansV]

The syntax for DateSerial is DateSerial(year,month,day).

VBA is quite forgiving: if you specify 0 for the day argument, you get the last day of the month previous to the one specified by year
and month.

DateSerial(2011,1,1) is January 1st, 2011, and DateSerial(2011,1,0) is the day before that: December 31st, 2010.

[Leesha]

Thanks Hans!!