Elapsed date & time in terms of days, to the second

[Guest]

Hello,

I receive a spreadsheet on a daily basis, and one column has a start date,
and then a corresponding column has the start time for that date. Also,
there is a third and fourth column with end dates in one, and end times in
another. I would to calculate the elapsed time between the start and end
dates, to the nearest second, weekends not to be included.
For example A1: Fri. 19-Nov-04, B1: 11:00:00 a.m.
C1: Mon. 22-Nov-04, D1: 11:00:00 p.m.
So the elapsed work time would be 1.5 days

If anyone knows how this can be accomplished, no matter the complexity of
the formula as long as it is explained well. Of course it is probably
something easy that I have completely missed.

Thank you for taking the time to read this question

-Dan Beaudoin

 

[JWolf]

E1:=NETWORKDAYS(A1,C1)+(D1-B1)

(NETWORKDAYS is part of the Analysis tool pack add-in, which must be
installed)

If the dates and numbers come in as text, they need to be converted to
use in the above formula, see date and time functions help.

Multiply the result in E1 by 86400 to see seconds. (86400=24hrs x 60
minutes x 60 seconds)

 

[Guest]

Hello JWolf,

Thank you for the advice, that is awesome, however, I have two questions.
1) Why is it when I pick the exact same date and times for the 4 columns,
that it gives me a response of 1, and not 0. 2) Is there a way to subtract
weekends?

Thanks

-DB

 

[Guest]

Hello JWolf,

Thanks for the advice, that is awesome, however, I have two questions
now...1) If I have the same start date and time, how come the result is 1,
and not 0. 2) Is there a way in which if the start date and end date falls
during a weekend, the weekend can be subtracted?

Thanks,

-DB