What formula can i use to add time?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
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
 
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
 
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.
 
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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top