Another Excel question!
Hi,
I'm creating an Excel worksheet for overtime. Our company rules say you can't claim from 07:30 onwards and not before 18:30 for the evening. I have three columns in the sheet - start time / end time and total hours. What I need to do is for the user to enter start and end time for the day and for Excel to total the overtime hours BUT taking the above into account... Anybody know what formula I shoud use for the total column? Cheers Lee |
Re: Another Excel question!
Would the datediff function be any use to you for this?
|
Re: Another Excel question!
I've no idea what I use or how I go about it...
---------- Post added at 16:35 ---------- Previous post was at 16:34 ---------- It's more time related i think - not sure if any functions available for that... |
Re: Another Excel question!
Is the third column "total hours" or "total overtime hours"?
That is, if someone worked from 15.30 to 19.30, would you want to see a "1" in column three? |
Re: Another Excel question!
Just use a sum.
If they worked until 19:30 then subtract 19:30 from 18:30. Leaves 1 hours overtime. Ensure your cell formats are set to HH:MM:SS. |
Re: Another Excel question!
Quote:
Total hours overtime.... 1 = correct! ---------- Post added at 16:44 ---------- Previous post was at 16:43 ---------- Quote:
|
Re: Another Excel question!
Quote:
=IF((TIMEVALUE("7:30 AM")-A2)<0,0,TIMEVALUE("7:30 AM")-A2)+IF((B2-TIMEVALUE("18:30"))<0,0,B2-TIMEVALUE("18:30")) She ain't pretty, but... Start Stop Overtime 08:00 16:00 00:00 07:00 16:00 00:30 08:00 19:00 00:30 07:00 19:00 01:00 |
Re: Another Excel question!
Quote:
=IF((TIMEVALUE("7:30 AM")-E10)<0,0,TIMEVALUE("7:30 AM")-E10)+IF((F10-TIMEVALUE("18:30"))<0,0,F10-TIMEVALUE("18:30")) 07:30 - 19:30 Result = 0.04 Should be 1 |
Re: Another Excel question!
1 Attachment(s)
Attached any good? Gives you the facility to change the start & end qualifying times if you need to...
|
Re: Another Excel question!
Quote:
Quote:
|
Re: Another Excel question!
Quote:
:tu: |
Re: Another Excel question!
Just hide columns C,D,F & G...
Protect the worksheet except for the entry cells... |
Re: Another Excel question!
Seems like I was to slow.
This is how I would have done it. Code:
=IF(A3>TEXT("07:30","HH:MM"),0,TEXT("07:30","HH:MM")-A3)+IF(B3>TEXT("18:30","HH:MM"),0,B3-TEXT("18:30","HH:MM")) |
Re: Another Excel question!
Quote:
Ahhhh......I thought I had - reset the formatting and BINGO! Thanks for the work....another one solved by CF! :tu::tu: :) |
Re: Another Excel question!
Quote:
|
All times are GMT +1. The time now is 10:13. |
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.