Calculate time

[Chip Pearson]

Mark,

Try something like

=(EndTime - StartTime)+(StartTime>EndTime)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

Mark,

The second half of the formula, +(StartTime>EndTime), is used to
correct the value if the StartTime is before midnight and the
EndTime is after midnight; for example, if you start at 9PM and
end at 3AM.

Times are stored as a fraction of a 24 hour day, so 9PM is stored
as 0.875 and 3AM is stored as 0.125. If you simply subtract
EndTime - StartTime, you'll end up with 0.125 - 0.875, or -0.75.
This is remedied by the second part of the formula.

If StartTime is greater than EndTime (9PM is later than 3AM), the
comparison StartTime>EndTime returns TRUE which is treated as 1.
This adds 1, the equivalent of 24 hours, to the result, which
makes the example formula 0.125 - 0.875 + 1 = 0.25, or 6:00.

In effect, the +(StartTime>EndTime) moves the EndTime in to the
following day.

For lots more information about working with dates and times in
Excel, see www.cpearson.com/excel/datetime.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

Mark,

Specifically what problems are you having when you try to sum the
times?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Mark Ruiz]

I have to keep time log for my business travel within the city. Does anyone
know of a formula that will calculate the difference of a start time and an
end time.

Pleas help!

MRuiz

 

[Mark Ruiz]

Thanks Chip,
That work perfect! I really don't understand the second part of that
formula but it works just fine.

Thnx for your time,

MRuiz

 

[Mark Ruiz]

Sorry Chip,
I can't get the sum of the individual times.

Thnx,

MRuiz

 

[Mark Ruiz]

Chip,
It was a formatting issue. Thanks for helping me out.
This will really save me alot of time.

Thnx,

MRuiz