Home |
Search |
Today's Posts |
#1
|
|||
|
|||
time
I am trying to find a formula that will help me with my employee timecards.
Our manual time clock posts time as follows 3:30PM is stamped as 15:50 3:45 is like 15:75....I am looking for way to calculate these and get the appropiate hours they have worked with out having to sit and look through a paper chart then do it all by hand. Any help would be awesome. Thanks. |
#2
|
|||
|
|||
Better punctuation would make your message clearer.
When you say "3:30PM is stamped as 15:50 3:45 is like 15:75", I assume you mean: "3:30pm is stamped 15:50. 3:45pm is stamped 15:75". I think the simplest would be to replace the colon with a period, then divide by 24 (which converts a number of hours to an Excel time). Try the following: =substitute(a1,":",".")/24 Format as time. -- Regards, Fred "SaTiVa" wrote in message ... I am trying to find a formula that will help me with my employee timecards. Our manual time clock posts time as follows 3:30PM is stamped as 15:50 3:45 is like 15:75....I am looking for way to calculate these and get the appropiate hours they have worked with out having to sit and look through a paper chart then do it all by hand. Any help would be awesome. Thanks. |
#3
|
|||
|
|||
I will try that formula. Our time clock uses 100ths. it goes from 15:00 -
15:99 for the time between an hour. I am looking to be able to calculate the hours the employees work: example if they clock in at 14:98 and clock out at 1:98. I am assuming the time is a form of military. "Fred Smith" wrote: Better punctuation would make your message clearer. When you say "3:30PM is stamped as 15:50 3:45 is like 15:75", I assume you mean: "3:30pm is stamped 15:50. 3:45pm is stamped 15:75". I think the simplest would be to replace the colon with a period, then divide by 24 (which converts a number of hours to an Excel time). Try the following: =substitute(a1,":",".")/24 Format as time. -- Regards, Fred "SaTiVa" wrote in message ... I am trying to find a formula that will help me with my employee timecards. Our manual time clock posts time as follows 3:30PM is stamped as 15:50 3:45 is like 15:75....I am looking for way to calculate these and get the appropiate hours they have worked with out having to sit and look through a paper chart then do it all by hand. Any help would be awesome. Thanks. |
#4
|
|||
|
|||
Hello, I actually created a sheet to calculate this. We too deal in military
time and 100 'click' time clocks. It is a straight math calculation. The only requirement on my part is that I 'round up' or 'round down' the punch in / out times to the nearest 15 minutes based on our punch chart. For instance punching in at 1505 is the same as 1500 (3:00pm)(on time) punching in at 1506 (late) is the same as 1525 (3:15pm). I would be happy to e-mail this attachment to you if you'd like. 2 options are available in the same file. opt 1 - a 30 minute lunch is automatically deducted when the formula returns hours worked as 6 and no lunch deducted for hours worked <=6. opt 2 - will still deduct a 30 min lunch for hours worked 6 but includes the option to type the in and out lunch punches (rounded to the nearest 15 min) and have this calculate (in case a lunch was longer than 30 min). If you have a particular criteria let me know, I can probably modify the one I use in very little time. provide a way to contact you and I will forward it Judy "SaTiVa" wrote: I am trying to find a formula that will help me with my employee timecards. Our manual time clock posts time as follows 3:30PM is stamped as 15:50 3:45 is like 15:75....I am looking for way to calculate these and get the appropiate hours they have worked with out having to sit and look through a paper chart then do it all by hand. Any help would be awesome. Thanks. |
#5
|
|||
|
|||
We are a c-store so no breaks are taken. We just calculate the time no
rounding to the next 5 or 15 intervel. If it is after the scheduled time in example: "you are to be there by 15:00 and you clock in at 15:01' you are late. And we always have to stay over a bit so we do it just up to the minute. We have approx 8 - 10 people we have to keep track of. If you would like you can contact me via email "Judy" wrote: Hello, I actually created a sheet to calculate this. We too deal in military time and 100 'click' time clocks. It is a straight math calculation. The only requirement on my part is that I 'round up' or 'round down' the punch in / out times to the nearest 15 minutes based on our punch chart. For instance punching in at 1505 is the same as 1500 (3:00pm)(on time) punching in at 1506 (late) is the same as 1525 (3:15pm). I would be happy to e-mail this attachment to you if you'd like. 2 options are available in the same file. opt 1 - a 30 minute lunch is automatically deducted when the formula returns hours worked as 6 and no lunch deducted for hours worked <=6. opt 2 - will still deduct a 30 min lunch for hours worked 6 but includes the option to type the in and out lunch punches (rounded to the nearest 15 min) and have this calculate (in case a lunch was longer than 30 min). If you have a particular criteria let me know, I can probably modify the one I use in very little time. provide a way to contact you and I will forward it Judy "SaTiVa" wrote: I am trying to find a formula that will help me with my employee timecards. Our manual time clock posts time as follows 3:30PM is stamped as 15:50 3:45 is like 15:75....I am looking for way to calculate these and get the appropiate hours they have worked with out having to sit and look through a paper chart then do it all by hand. Any help would be awesome. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time and motion chart | Charts and Charting in Excel | |||
Excel Time Manipulation | Excel Discussion (Misc queries) | |||
conditional formatting with time values | Excel Discussion (Misc queries) | |||
Time Sheets | New Users to Excel | |||
unmet challenge | Excel Worksheet Functions |