datediff with decimal

[Basil]

I think I hate the datediff function... someone please
restore my confidence:

I need to compare 2 dates (start/end) and return the
difference in months - but I need it to give a decimal
difference.

I'd prefer the decimal to be calculated according to the
length of the [end] month eg 01/10/2003 (1st October - I'm
from the UK!) to 07/11/2003 = 1.2 since there are 30 days
in November (6/30=0.2). But if it is another way eg
average length of month in year then that is fine.

Anyone have any idea??

Basil
PS If anyone knows why snapshot viewer won't show anything
but the title of a report yet seems to print out the whole
lot, let me know.

 

[Rick Brandt]

I think I hate the datediff function... someone please
restore my confidence:

I need to compare 2 dates (start/end) and return the
difference in months - but I need it to give a decimal
difference.

I'd prefer the decimal to be calculated according to the
length of the [end] month eg 01/10/2003 (1st October - I'm
from the UK!) to 07/11/2003 = 1.2 since there are 30 days
in November (6/30=0.2). But if it is another way eg
average length of month in year then that is fine.

Anyone have any idea??

DateDiff doesn't do fractional return values. If that's what you want you
need to use DateDiff () to return the numbers of days and then do the math
yourself to produce the number of months that includes a decimal. There
would be an inherent inaccuracy since not all months have the same number
of days though.

 

[Basil]

-----Original Message-----

I think I hate the datediff function... someone please
restore my confidence:

I need to compare 2 dates (start/end) and return the
difference in months - but I need it to give a decimal
difference.

I'd prefer the decimal to be calculated according to the
length of the [end] month eg 01/10/2003 (1st October - I'm
from the UK!) to 07/11/2003 = 1.2 since there are 30 days
in November (6/30=0.2). But if it is another way eg
average length of month in year then that is fine.

Anyone have any idea??

DateDiff doesn't do fractional return values. If that's what you want you
need to use DateDiff () to return the numbers of days and then do the math
yourself to produce the number of months that includes a decimal. There
would be an inherent inaccuracy since not all months have the same number
of days though.

Cheers Rick, fair point. Is there a simple formula that
will return the number of days in a given month? I think I
prefer formulas in Excel.

PS I do occasionally check the email given above.. I've
found that the junk e-mails generally gives me a complex
about needing penis enlargement... I hope it aintz just me
dat gets dem der e-mails!

 

[Rick Brandt]

Cheers Rick, fair point. Is there a simple formula that
will return the number of days in a given month? I think I
prefer formulas in Excel.

If you substitute integer values for the two variables below it will return
the number of days in that month. Don't how much good that does you
though. If your two dates are six months apart you need to know how many
days is all of those months don't you?

DaysInMonth = Day(DateSerial(YearVariable, MonthVariable + 1, 0))