Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to calculate time in a payroll worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to calculate time in a payroll worksheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to calculate time in a payroll worksheet
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to calculate time in a payroll worksheet -- CAUTION
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" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to calculate time in a payroll worksheet -- CAUTION
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" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to calculate time in a payroll worksheet -- Shifts that cross midnight
If your shifts cross midnight, you can use the following formula in A3:
=IF(A2<A1,(1-A1)+A2,A2-A1). "PJF" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to calculate time in a payroll worksheet
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to calculate time in a payroll worksheet
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to calculate time in a payroll worksheet
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to calculate time for a payroll sheet.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can excel calculate payroll taxes? | Excel Worksheet Functions | |||
How do I calculate total weekly hours for payroll in Excel? | Excel Discussion (Misc queries) | |||
How do you calculate payroll with tips? | Excel Discussion (Misc queries) | |||
how do i make overtime in my payroll sheet calculate after 42.5 h. | Excel Worksheet Functions | |||
How do I set up payroll for 1 full time and 1 part time employee? | New Users to Excel |