Our time is off on payroll about 6 minutes. It is not our time clock. I checked the formulas and the time value formula comes back as false for the sum of the time clocking in and clocking out. I’m not too familiar with this. Just took this over. Can someone help with this his?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Excel Facts
Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I think there is some confusion here, I see nothing wrong with the file.
If you look at Sheet "05080521", In B6:D6, they worked from 6:56 AM - 3:09 PM. 8 Hours and 13 minutes or 8.22 Hours
Since time is expressed as a decimal in Excel:
An hour is 0.41667 (1/24) and a minute is 0.000694 (0.41667/60)
8 Hours (0.41667x8) and 13 minutes (0.000694x13) is 0.342.
24*0.342 to get this back out of decimal and into hours is 8.22, as is in the cell.
Does this clear things up?
To assist here, you can change your time formatted cells to number and see what they look like, as for "TIMEVALUE("12:00:00")" this is exactly the same as "0.5" or 12:00. So not really needed unless it's hard to get your head around the decimal time.
If you look at Sheet "05080521", In B6:D6, they worked from 6:56 AM - 3:09 PM. 8 Hours and 13 minutes or 8.22 Hours
Since time is expressed as a decimal in Excel:
An hour is 0.41667 (1/24) and a minute is 0.000694 (0.41667/60)
8 Hours (0.41667x8) and 13 minutes (0.000694x13) is 0.342.
24*0.342 to get this back out of decimal and into hours is 8.22, as is in the cell.
Does this clear things up?
To assist here, you can change your time formatted cells to number and see what they look like, as for "TIMEVALUE("12:00:00")" this is exactly the same as "0.5" or 12:00. So not really needed unless it's hard to get your head around the decimal time.
Last edited:
Upvote
0
Okay so there is some confusion because when I do the time cards and then my GM does the checks he feels as though everyone's time is off. So the decimal I guess does pose an issue. So if I take the decimal out then it will show the 8.13?
Upvote
0
It's not quite that simple.
You need to convert the .22 from decimal to minutes (/100 *60). There must be a simpler way, but this seems to do the job.
In G5:
=ROUND(INT(IF(AND(E5>=TIMEVALUE("12:00:00"),F5<=TIMEVALUE("12:00:00")),(F5+(1-E5))*24,(F5-E5)*24))+(IF(AND(E5>=TIMEVALUE("12:00:00"),F5<=TIMEVALUE("12:00:00")),(F5+(1-E5))*24,(F5-E5)*24)-INT(IF(AND(E5>=TIMEVALUE("12:00:00"),F5<=TIMEVALUE("12:00:00")),(F5+(1-E5))*24,(F5-E5)*24)))/100*60,2)
You need to convert the .22 from decimal to minutes (/100 *60). There must be a simpler way, but this seems to do the job.
In G5:
=ROUND(INT(IF(AND(E5>=TIMEVALUE("12:00:00"),F5<=TIMEVALUE("12:00:00")),(F5+(1-E5))*24,(F5-E5)*24))+(IF(AND(E5>=TIMEVALUE("12:00:00"),F5<=TIMEVALUE("12:00:00")),(F5+(1-E5))*24,(F5-E5)*24)-INT(IF(AND(E5>=TIMEVALUE("12:00:00"),F5<=TIMEVALUE("12:00:00")),(F5+(1-E5))*24,(F5-E5)*24)))/100*60,2)
Upvote
0
barry houdini
MrExcel MVP
- Joined
- Mar 23, 2005
- Messages
- 20,825
If the time difference is 8 hours 13 minutes then usually it's best to display either as a time value, 8:13 or as a decimal 8.22. If you don't do that and display as 8.13 then that will give you the wrong amount if multiplied by an hourly rate, for example.
To get the result as a time value like 8:13 then this formula should be sufficient in G5
=MOD(F5-E5,1)
Format G5 as a time value
To get the result as a time value like 8:13 then this formula should be sufficient in G5
=MOD(F5-E5,1)
Format G5 as a time value
Upvote
0
