Switching Date Format for Expressions

[proballin]

So I have two dates, [Start Date] and [End Date]. These are in mm/dd/yyyy format. I would like to subtract them in order to give me the amount of days in between the two dates. However the kicker is that I need to convert each of the days to be the first of the month before subtracting so that I get the exact days from day 1 of one month minus day 1 of another month. Example:

[Start Date] = 12/23/2004
[End Date] = 5/15/2005

I need the start date to be 12/1/2004 and the end date to be 5/1/2005 before subtracting so that I can find the exact amount of days between months. Any suggestions on getting this into an expression in a report?

 

[Rabbie]

To convert the dates try this

[Start Date] = Dateserial(Year([Start Date]),month([Start date]),1)
 
[End Date] = Dateserial(Year([End Date]),month([End date]),1)

 

[proballin]

Ok...that works...however I am getting the wrong amount of days. When I do the following equation I get 243 days when I should be getting closer to 270. Any clue as to why?

=DateDiff("d",(DateSerial(Year(Date()),Month(Date()),1)),"9/1/2009")

 

[pbaldy]

January 1 to September 1 is 8 months, 8 x 30 = 240, so it looks correct to me.

 

[proballin]

Yep it is right...the numbers I was checking it against at an extra month added that I didnt catch. Thanks though!