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

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

--
Regards,
Fred


"lili" wrote in message
...
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

This does not work from PM to AM

"Fred Smith" wrote:

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

--
Regards,
Fred


"lili" wrote in message
...
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

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.
--
David Biddulph

"lili" wrote in message
...
This does not work from PM to AM

"Fred Smith" wrote:

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

--
Regards,
Fred


"lili" wrote in message
...
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

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


with @tiscali.co.uk


"lili" wrote in message
...
This does not work from PM to AM

"Fred Smith" wrote:

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

--
Regards,
Fred


"lili" wrote in message
...
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

A1: start time
B1: end time

=(B1-A1)*24


"lili" wrote:

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