Subtracting Time in Excel

[Guest]

My employer does payroll manually. They look at timecard timestamps and then calculate how many hours I worked. Then they forward that data to the company that prints the paychecks. My employer is often wrong on calculations. I am trying to do a worksheet where I enter my start/stop times and get the correct results to check against employer's calcs. Doing an AM to PM shift is no problem, calculations are correct. However when I try to get hours worked from a Start PM to Stop AM ( let's say 11:09 PM - 7:53 AM) shift all I can get is #######. Does anyone have a tip on how to do this calculation correctly? Thanx

 

[Norman Harker]

Hi Ed!

Use:
=B1-A1+(B1<A1)

If the start time is greater than the stop time B1<A1 returns TRUE
which in a calculation is coerced to the value of 1.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

My employer does payroll manually. They look at timecard timestamps

and then calculate how many hours I worked. Then they forward that
data to the company that prints the paychecks. My employer is often
wrong on calculations. I am trying to do a worksheet where I enter my
start/stop times and get the correct results to check against
employer's calcs. Doing an AM to PM shift is no problem, calculations
are correct. However when I try to get hours worked from a Start PM
to Stop AM ( let's say 11:09 PM - 7:53 AM) shift all I can get is
#######. Does anyone have a tip on how to do this calculation
correctly? Thanx

 

[Guest]

Norman
I don't understand what you just did, and can't believe it looks so simple, but it sure does work
Thank you very much

 

[Norman Harker]

Hi Ed!

Always try and understand the solutions or we're wasting our time (me
*and* you).

B1-A1 is the "normal formula"

Remember that time is recorded by Excel as a decimal part of a day.

Trouble is that if you work over midnight, then the finishing time
decimal will be smaller than the starting time decimal.

Assuming Excel with 1900 date system could do it:

9:00PM to 3:00AM would be 0.875 - 0.125 = -0.75

But if I add 1 then the answer is -0.75+1 = .25 which is 6 hours.

I only want to add 1 if B1<A1

=B1<A1
Returns TRUE if B1<A1 and otherwise is FALSE

TRUE if put in a mathematical expression is "coerced" to 1 and FALSE
is "coerced" to 0

So I use:
=B1-A1+(B1<A1)

Normal "intraday" calculations will have B1>A1 so we'll get B1-A1+0
Where time spans Midnight we will have B1<A1 so well get B1-A1+1

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

Norman
Too cool! Now I understand and am grateful that you took the time to explain it to me
Thank you once again, have a great day
E

 

[Norman Harker]

Hi Ed!

Thanks for thanks is appreciated.

Never hesitate to ask for an explanation as there's a large number of
peculiarities and approaches that won't immediately hit the eye (or brain).

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.