datediff with decimal

  • Thread starter Thread starter Basil
  • Start date Start date
B

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.
 
Basil said:
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??
Click to expand...

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.
 
-----Original Message-----
Basil said:
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??
Click to expand...

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.
Click to expand...

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!
 
Basil said:
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.
Click to expand...

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))
 
Back
Top