asked on

Difference between two dates

Afternoon

I need to workout the difference between two dates. Although i have two twists to the calculation.

Always a Date (dd/mm/yyyy hh:mm:ss) in A1,
If there is a date in B1 (dd/mm/yyyy hh:mm:ss), then calculate the difference between the two.
If not then calculate the difference between Now() and A1.
This is the formula I have used and works.
IF(ISBLANK(A1),"",(IF(ISBLANK(B1),(NOW()-A1),(B1-A1))

Although I need to take into account to calculate only working days (mon-Fri) which I am afraid is beyond me.

It would be nice to have it stored in the VBA code although if it can only be done as a formula then it will do.

please could you assist in finding me a solution.

Thanks in advance.

nutsch

If you install the analysis toolpak (tools \ add-ins) you can use the networkdays formula, as in

=IF(ISBLANK(A1),"",(IF(ISBLANK(B1),NETWORKDAYS(A1,NOW()),NETWORKDAYS(A1,B1))))

Thomas

barry houdini

Perhaps try this for a shorter formula. NETWORKDAYS counts both start and end date so I assumed you'd want to subtract 1......

=IF(A1="","",NETWORKDAYS(A1,IF(B1="",TODAY(),B1))-1)

regards, barry

Q_evans

ASKER

Afternoon

Thanks guys but this is not giving me the result I require as it doesnt give me to the nearest minute. It rounds it off to days, I think this it attributed to the NetworkDays function as I have tried this already and come to the same problem.

any other ideas?

nutsch

try separating the time calculation and the day calculation,on barry's formula

=IF(A1="","",NETWORKDAYS(A1,IF(B1="",TODAY(),B1))-1)+mod(IF(B1="",TODAY(),B1)-a1,1)

Q_evans

ASKER

Hi guys

That now gives me to nearest hour, I really do need to the nearest minute.

you guys are a great help.

Q_evans

ASKER

working further with the formula, If B1 is blank, Then it appears to lose a day? but calculates to the minute.

byundt

If you plug NOW into Thomas' formula, does that give you the results you want?
=NETWORKDAYS(A1,IF(B1="",NOW(),B1))-1+MOD(IF(B1="",NOW(),B1)-A1,1)

I formatted the results as d hh:mm

barry houdini

I assume that you want to count 24 hours a day on Mondays to Fridays, e.g. 17:00 on Friday to 07:00 on the following Monday would give you just 14:00 hours (17:00 to Midnight on Friday and then midnight to 07:00 on Monday).

That's what Brad's formula will give you, but two observations:

d:hh:mm format is only good for time periods up to 31 days - if you have periods greater than that then you'd to format as [h]:mm for total hours or alter the formula to the following

=NETWORKDAYS(A1,IF(B1="",NOW(),B1))-1-(MOD(IF(B1="",NOW(),B1)-A1,1)<0)&" days "&TEXT(IF(B1="",NOW(),B1)-A1,"h:mm")

which will give you a result like "35 days 13:45"

If either start date, end date (or "now" if end date is blank) might be at the weekend then you'd need a more complex formula......

regards, barry

Q_evans

ASKER

Thanks, This question is still unaswered,

I have been using this formula
=NETWORKDAYS(A1,IF(B1="",NOW(),B1))-1+MOD(IF(B1="",NOW(),B1)-A1,1)
Although once the calculation between 1 day and two days seems to falter, eg

18/05/2011 17:51:53 from Now (which at the time was 18/05/2011 22:52:00 give me the answer of 01:05:01 (dd:HH:mm), where is gains an extra day from I am not sure?

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tracy

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.