how to calculate time in a payroll worksheet

[Peekabeaux]

I am attempting to create a time card in which the user just types in the
time they clock out and type in the time they clock out. At the end of the
day I want to total number of hours worked. But when I use a time format,
and enter 8 for 8:00 a.m, the value shows 1/8/1900 at 12:00 a.m.. How do I
fix this so only the hours and minutes are shown and I can calculate the
daily and weekly totals? I have tried downloading 2 different templates from
Microsoft, but still shows the date. Please help.
Oh yeah, while I'm asking :-), does anyone out there know how to insert a
cell value in a header. I want to apply this to a template.
Thanks,

Peek

[Paul D.]

If you are entering the times right into a worksheet, go to the DATA menu
and try VALIDATION... or format the field to the time format you like.
You must enter the time as an hour and minute separated by a ':'.
8: however, will enter as 8:00.

If you are entering the times into a userform, there are a couple options.
"Peekabeaux" wrote:

> I am attempting to create a time card in which the user just types in the
> time they clock out and type in the time they clock out. At the end of the
> day I want to total number of hours worked. But when I use a time format,
> and enter 8 for 8:00 a.m, the value shows 1/8/1900 at 12:00 a.m.. How do I
> fix this so only the hours and minutes are shown and I can calculate the
> daily and weekly totals? I have tried downloading 2 different templates from
> Microsoft, but still shows the date. Please help.
> Oh yeah, while I'm asking :-), does anyone out there know how to insert a
> cell value in a header. I want to apply this to a template.
> Thanks,
>
> Peek

[PJF]

Here's a crude way: Use 24-hour (Military) time to enter the start and end
times. Be sure to insert a colon between hours and minutes. So, if an
employee works 7AM to 3:30 PM, the entries would be 7:00 and 15:30,
respectively. You can format the cells so that they display English time,
i.e., 7:00 AM and 3:30 PM, respectively. So, if you put the start time in
cell A1 and the end time in A2, you can use a simple subtraction formula in
cell A3, formatting it as a simple number. It will display as a decimal.
You must multiply the decimal by 24 in order to get a display of hours
worked.

So, the employee inserts :

08:00 in cell A1 and will display as 8:00 AM
15:30 in cell A2 and will display as 3:30 PM

The total time worked is then calculated in A3 by the formula: =(A2-A1)*24
and will display as 7.5. If you'd rather retain a time display in A3
(7:30), change the format and delete the 24-hour multiplier.

Hope this helps.




"Peekabeaux" <[email protected]> wrote in message
news:[email protected]...
> I am attempting to create a time card in which the user just types in the
> time they clock out and type in the time they clock out. At the end of
the
> day I want to total number of hours worked. But when I use a time format,
> and enter 8 for 8:00 a.m, the value shows 1/8/1900 at 12:00 a.m.. How do
I
> fix this so only the hours and minutes are shown and I can calculate the
> daily and weekly totals? I have tried downloading 2 different templates
from
> Microsoft, but still shows the date. Please help.
> Oh yeah, while I'm asking :-), does anyone out there know how to insert a
> cell value in a header. I want to apply this to a template.
> Thanks,
>
> Peek

[PJF]

P.S. CAUTION: This will only work for shifts that do not cross midnight. If
you have an 11 PM to 7 AM shift, this won't work. Will see if I can come up
with a solution.


"PJF" <[email protected]> wrote in message
news:[email protected]...
> Here's a crude way: Use 24-hour (Military) time to enter the start and
end
> times. Be sure to insert a colon between hours and minutes. So, if an
> employee works 7AM to 3:30 PM, the entries would be 7:00 and 15:30,
> respectively. You can format the cells so that they display English time,
> i.e., 7:00 AM and 3:30 PM, respectively. So, if you put the start time in
> cell A1 and the end time in A2, you can use a simple subtraction formula
in
> cell A3, formatting it as a simple number. It will display as a decimal.
> You must multiply the decimal by 24 in order to get a display of hours
> worked.
>
> So, the employee inserts :
>
> 08:00 in cell A1 and will display as 8:00 AM
> 15:30 in cell A2 and will display as 3:30 PM
>
> The total time worked is then calculated in A3 by the formula: =(A2-A1)*24
> and will display as 7.5. If you'd rather retain a time display in A3
> (7:30), change the format and delete the 24-hour multiplier.
>
> Hope this helps.
>
>
>
>
> "Peekabeaux" <[email protected]> wrote in message
> news:[email protected]...
> > I am attempting to create a time card in which the user just types in
the
> > time they clock out and type in the time they clock out. At the end of
> the
> > day I want to total number of hours worked. But when I use a time
format,
> > and enter 8 for 8:00 a.m, the value shows 1/8/1900 at 12:00 a.m.. How
do
> I
> > fix this so only the hours and minutes are shown and I can calculate the
> > daily and weekly totals? I have tried downloading 2 different templates
> from
> > Microsoft, but still shows the date. Please help.
> > Oh yeah, while I'm asking :-), does anyone out there know how to insert
a
> > cell value in a header. I want to apply this to a template.
> > Thanks,
> >
> > Peek
>
>

[Patrick Molloy]

remember that Excel saves time internally as teh decimal part of a day. So
6AM is 0.25 and 12 noon is 0.5
One check for times that roll to the next day would be to add '1' if the end
time is earlier than the start time....1 of course is in Excel terms, 1 day,
thus 24 hours.

"PJF" wrote:

> P.S. CAUTION: This will only work for shifts that do not cross midnight. If
> you have an 11 PM to 7 AM shift, this won't work. Will see if I can come up
> with a solution.
>
>
> "PJF" <[email protected]> wrote in message
> news:[email protected]...
> > Here's a crude way: Use 24-hour (Military) time to enter the start and
> end
> > times. Be sure to insert a colon between hours and minutes. So, if an
> > employee works 7AM to 3:30 PM, the entries would be 7:00 and 15:30,
> > respectively. You can format the cells so that they display English time,
> > i.e., 7:00 AM and 3:30 PM, respectively. So, if you put the start time in
> > cell A1 and the end time in A2, you can use a simple subtraction formula
> in
> > cell A3, formatting it as a simple number. It will display as a decimal.
> > You must multiply the decimal by 24 in order to get a display of hours
> > worked.
> >
> > So, the employee inserts :
> >
> > 08:00 in cell A1 and will display as 8:00 AM
> > 15:30 in cell A2 and will display as 3:30 PM
> >
> > The total time worked is then calculated in A3 by the formula: =(A2-A1)*24
> > and will display as 7.5. If you'd rather retain a time display in A3
> > (7:30), change the format and delete the 24-hour multiplier.
> >
> > Hope this helps.
> >
> >
> >
> >
> > "Peekabeaux" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am attempting to create a time card in which the user just types in
> the
> > > time they clock out and type in the time they clock out. At the end of
> > the
> > > day I want to total number of hours worked. But when I use a time
> format,
> > > and enter 8 for 8:00 a.m, the value shows 1/8/1900 at 12:00 a.m.. How
> do
> > I
> > > fix this so only the hours and minutes are shown and I can calculate the
> > > daily and weekly totals? I have tried downloading 2 different templates
> > from
> > > Microsoft, but still shows the date. Please help.
> > > Oh yeah, while I'm asking :-), does anyone out there know how to insert
> a
> > > cell value in a header. I want to apply this to a template.
> > > Thanks,
> > >
> > > Peek
> >
> >
>
>
>

[PJF]

If your shifts cross midnight, you can use the following formula in A3:
=IF(A2<A1,(1-A1)+A2,A2-A1).


"PJF" <[email protected]> wrote in message
news:[email protected]...
> P.S. CAUTION: This will only work for shifts that do not cross midnight.
If
> you have an 11 PM to 7 AM shift, this won't work. Will see if I can come
up
> with a solution.
>
>
> "PJF" <[email protected]> wrote in message
> news:[email protected]...
> > Here's a crude way: Use 24-hour (Military) time to enter the start and
> end
> > times. Be sure to insert a colon between hours and minutes. So, if an
> > employee works 7AM to 3:30 PM, the entries would be 7:00 and 15:30,
> > respectively. You can format the cells so that they display English
time,
> > i.e., 7:00 AM and 3:30 PM, respectively. So, if you put the start time
in
> > cell A1 and the end time in A2, you can use a simple subtraction formula
> in
> > cell A3, formatting it as a simple number. It will display as a
decimal.
> > You must multiply the decimal by 24 in order to get a display of hours
> > worked.
> >
> > So, the employee inserts :
> >
> > 08:00 in cell A1 and will display as 8:00 AM
> > 15:30 in cell A2 and will display as 3:30 PM
> >
> > The total time worked is then calculated in A3 by the formula:
=(A2-A1)*24
> > and will display as 7.5. If you'd rather retain a time display in A3
> > (7:30), change the format and delete the 24-hour multiplier.
> >
> > Hope this helps.
> >
> >
> >
> >
> > "Peekabeaux" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am attempting to create a time card in which the user just types in
> the
> > > time they clock out and type in the time they clock out. At the end
of
> > the
> > > day I want to total number of hours worked. But when I use a time
> format,
> > > and enter 8 for 8:00 a.m, the value shows 1/8/1900 at 12:00 a.m.. How
> do
> > I
> > > fix this so only the hours and minutes are shown and I can calculate
the
> > > daily and weekly totals? I have tried downloading 2 different
templates
> > from
> > > Microsoft, but still shows the date. Please help.
> > > Oh yeah, while I'm asking :-), does anyone out there know how to
insert
> a
> > > cell value in a header. I want to apply this to a template.
> > > Thanks,
> > >
> > > Peek
> >
> >
>
>

[DaveP]

Peek,

Assuming you are working with values within the same day, you can enter
clock in and out times as numbers (eg 800 for 8am, 830 for 8:30am etc)
and then do a subtraction on the start and finish time using the
following formula:

A1 = 800, B1 = 1200, C1 =
"=(REPLACE(B1,LEN(B1)-1,0,":")-(REPLACE(A1,LEN(A1)-1,0,":")))

Then format C1 as "HH:MM"

This would give a result of 4:00.

HTH

Dave

[TCrow2000]

I ran into the same problem as you. I was trying to come up with a way
to print out my schedule and taking it a step further, wanted to
estimate how much my net pay would be. The formula I came up with with
the result in cell A3 is:

=IF((A2-A1)*24<0,((A2-A1)*24)+24,(A2-A1)*24)

As in the other suggestions, you need to use military 24 hour time
format to input the time.

Hope it works for you.
TCrow

[DaleP1]

All the information in this thread is very helpful, I could use just a bit
more help with this please. My time sheet is set up like the brief example
below.

A1 A2 A3 A4 A5 A6
0600 1400 off off 0600 1400

This sheet shows a schedule for each employee. on 1 line each for 2 weeks.
I am not doing well at tying the previous examples into a working formula
over a range of cells.
I just want it to sum up the hours at the end of a row for each person.
I sincerely appreciate any help Thanks

"TCrow2000" wrote:

> I ran into the same problem as you. I was trying to come up with a way
> to print out my schedule and taking it a step further, wanted to
> estimate how much my net pay would be. The formula I came up with with
> the result in cell A3 is:
>
> =IF((A2-A1)*24<0,((A2-A1)*24)+24,(A2-A1)*24)
>
> As in the other suggestions, you need to use military 24 hour time
> format to input the time.
>
> Hope it works for you.
> TCrow
>
>

[ELIZE TALJAARD]

My time sheet is set up like the brief example below:

A B C D E
F
1 DAY: DATE: TIME IN: TIME OUT: Total Hours:
2 Thursday 1 11:12:00 01:58:00 = ?????-formula??
3 Friday 2 10:44:00 17:43:00 = ?????-formula??

I would like to know which formula to enter in F2, F3, etc. to be able to
obtain the total hours worked per day.

Help would be much appreciated. THANKS, ELIZE TALJAARD