What formula can i use to add time?

L

lili

example Punch in 6:45AM , Punch out 5:30PM

What formula can I use to add the total time worked by quarter hours? like
10.45
 
L

lili

That works only with side by side cells. not with two blank cells in between
for lunch in and lunch out. But it didn't work until I entered a negative
24, like =(B1-A1)*-12 Then it does give you the correct hours.
Thanks I learned something new, but can it be done with two cells between.
Or even with lunch in and lunch out times in between?
 
F

Fred Smith

To me, you are subtracting times, not adding them. If 5:30pm is in B1, and
6:45am is in A1, the difference between these two is:

=b1-a1

Format the result as a time, and you'll get 10:45

If you want the result in decimal hours, multiply by 24 (because Excel stores
times as fractions of a day), as in:

=(b1-a1)*24

Format as a number, and you'll get 10.75
 
L

lili

This does not work from PM to AM

Fred Smith said:
To me, you are subtracting times, not adding them. If 5:30pm is in B1, and
6:45am is in A1, the difference between these two is:

=b1-a1

Format the result as a time, and you'll get 10:45

If you want the result in decimal hours, multiply by 24 (because Excel stores
times as fractions of a day), as in:

=(b1-a1)*24

Format as a number, and you'll get 10.75
Click to expand...
 
D

David Biddulph

Put a space before the AM or PM.
5:30 pm is in B1, and 6:45 am in A1
=B1-A1 format as h:mm, or as [h]:mm if you're going to need to sum values
beyong 24 hours.
 
S

Sandy Mann

This does not work from PM to AM

For all times, including those that cross midnight use:

=MOD(B1-A1,1)

To round it to the nearest 15 minutes use:

=ROUND(MOD(B1-A1,1)/(0.25/24),0)*(0.25/24)

To round each time to the nearest 15 minutes before subtracting them use:

=MOD(ROUND(B1/(0.25/24),0)*(0.25/24)-ROUND(A1/(0.25/24),0)*(0.25/24),1)

Note that this may be uo to 30 minures different depending on the
Clock-on/Clock-off times.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
Top