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.

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.

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.

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.

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.