Returning a Decimal Place from DateDiff Function

[lloydmav]

Hi All,

I was wondering how I could return number of weeks between two dates but getting the result including at least 4 decimal places. I know that the datediff function returns an integer so I'm guessing this function won't do the job. I'm working in Excel by the way.

I know I could just return the number of days and then divide by seven and put that into a double variable but I need the decimal places to change every time I refresh them and doing it this way they would only change once a day when the day value changes. So I guess the question is also can I return the day value in decimal places

Does anyone have any suggestions on how to tackle this?

Thanks

Lloyd

[si_the_geek]

Hi lloydmav, welcome to VBForums!

You are right that DateDiff will only return integer values, and your thinking of using a smaller period then dividing is the way to get the value to 4 dp's.

I would recommend going for a period smaller than days, possibly hours (or minutes) instead. You would then divide by appropriate values, eg:

your_value = (datediff_value) / 24 / 7

[techgnome]

To get the number of weeks in DateDiff, use "ww" as the first parameter.

-tg

[lloydmav]

Hi lloydmav, welcome to VBForums!

You are right that DateDiff will only return integer values, and your thinking of using a smaller period then dividing is the way to get the value to 4 dp's.

I would recommend going for a period smaller than days, possibly hours (or minutes) instead. You would then divide by appropriate values, eg:

your_value = (datediff_value) / 24 / 7

Ah of course, start from a smaller number, thats great, thanks for the help

[Ecniv]

Dates are numbers.. 1 = 1 day.
So you could just minus one from the other and divide by 7... (you may need cdbl function if in code to force into a double).

Just another way