I know the title is stupid...
I am making a timesheet for my employees. On the sheet I have to put in when the arrive and when they quit. I have the time format 00:00 on the cells.
For ex. one arrives at 08:00 (cell A1) and leaves at 15:45 (cell A2).
What formula do I put in A3 so the outcome will be 7,75 hours?
Last edited by olafurbj; 09-16-2011 at 10:22 AM.
This should work for you =TEXT((A2-A1)*24,"##.##")
HTML Code:
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Note that Andrew-R's solution will give you a text output rather than numeric so may present problems if you want to do further arithmetic with it.
Regards
Andrew's formula worked for me, I added two dashes more to get the two decimal places to make it accurate.
=TEXT((A2-A1)*24,"##.##,##")
Do you want a TEXT return?
=(A2-A1)*24 will produce a simple numeric value, 7.75 in this case.
Regards
When I look better into it you're right TMShucks.
Your solution works better for me. Thank you.
<Super-villain>You win this round, TMShucks, but I'll be back!</S-V>
Ok, Andrew-R, try to solve this problem for me:
I've got this formula that calculates overtime for me. Overtime is past 16:00. This is the formula I put together and it works fine, except when an employee does not work all the way to 16:00, for ex. 15:45
=IF(D6>"16:00";0;(D6-"16:00")*24)
If an employee works from 08:00 till 16:45, the formula delivers 0,75 overtime hours.
BUT, if an employee works from 08:00 until 15:45, the formula delivers -0,25.
What do I add to the formula so the total doesn't go below 0 ?
Hi olafurbj
Try this, Put 16:00 in another cell, e.g C6 and lock the equation to that cell $C$6,,, when you copy the equation to other cells it will continue to look at C6
=IF(D6>$C$6,D6-$C$6,0)*24
JT
Last edited by JamesT1; 09-16-2011 at 12:16 PM.
I'd be inclined to use TIME rather than a text value ... and I think you have the outcomes the wrong way around. Try:
=IF(D6>TIME(16,0,0),(D6-TIME(16,0,0))*24,0)
though you may need to change the commas to semi-colons.
Regards
TMShucks, this is how I should put your formula into the Function Arguments column?:
IF
Logical_test D7>TIME(16;0;0)
Value_if_true D7-TIME(16;0;0)*24
Value_if_fase 0
If an employee works until 16:45 this formula delivers -15,30208333.
What am I doing wrong?
I think:
Logical_test D7>TIME(16;0;0)
Value_if_true (D7-TIME(16;0;0))*24
Value_if_fase 0
D7 is a time; TIME(16;0;0) is a time; so (D7-TIME(16;0;0)) is a time; (D7-TIME(16;0;0))*24 is a number.
Regards
GENIUS.Originally Posted by TMShucks
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks