What formula can i use to add time? |
|
Author |
Message |
fasthands
|
Posted: Tue Mar 27 19:13:51 CDT 2007 |
Top |
worksheet functions >> What formula can i use to add time?
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
Excel412
|
|
|
|
|
lili
|
Posted: Tue Mar 27 19:13:51 CDT 2007 |
Top |
worksheet functions >> What formula can i use to add time?
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?
> A1: start time
> B1: end time
>
> =(B1-A1)*24
>
>
>
> > 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
|
|
|
|
|
Fred
|
Posted: Tue Mar 27 20:31:53 CDT 2007 |
Top |
worksheet functions >> What formula can i use to add time?
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
> 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
|
|
|
|
|
Teethlessmama
|
Posted: Tue Mar 27 20:57:50 CDT 2007 |
Top |
worksheet functions >> What formula can i use to add time?
A1: start time
B1: end time
=(B1-A1)*24
> 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
|
|
|
|
|
lili
|
Posted: Tue Mar 27 21:28:07 CDT 2007 |
Top |
worksheet functions >> What formula can i use to add time?
This does not work from PM to AM
> 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
>
>
> > 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
>
>
>
|
|
|
|
|
David
|
Posted: Tue Mar 27 21:44:25 CDT 2007 |
Top |
worksheet functions >> What formula can i use to add time?
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
> This does not work from PM to AM
>
>
>> 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
>>
>>
>> > 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
>>
>>
>>
|
|
|
|
|
Sandy
|
Posted: Wed Mar 28 06:43:16 CDT 2007 |
Top |
worksheet functions >> What formula can i use to add time?
> 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
> This does not work from PM to AM
>
>
>> 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
>>
>>
>> > 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
>>
>>
>>
|
|
|
|
|
|
|