Hi,
I'm having an issue I cannot solve since I'm not used to Excel. I created a spreadsheet to calculate hours done by staff, but the issue is that it's a nightclub and most hours are done from PM to AM, so if I put in a shift from 22:55 till 6:20am (for example) it won't calculate the proper hours. For that example it would show -16.58 hours, instead of 7.42 hours. What's the format code to use in this case when shifts goes past to the next day? I'm currently using '=(A-B)*24'.
Cheers
If the start time is in A1 and the end time is in B1
format as h:mmFormula:
Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I suspect you are using to use the full date and time for shift start and end, instead of just time. Otherwise your formula would not return a negative number, it would fail with a result of "####" to fill the cell. The most straightforward and most accurate solution is to display the full date and time, and enter the correct date. Then your formula can stay the same.
It is possible to do a kludgy formula that will work with your data, but I'm not sure my assumption about your data is correct. If start time is in column A and end time is in column B, then try this:
Formula:Please Login or Register to view this content.
If you attach your file (remove the names first) then I can see your data, and I can just modify your file for you, showing you both techniques.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Hi,
Thanks for the quick reply!
I formatted as H:MM but your formula is getting me 7.25 hours for 22:55 till 06:20am and for some reason it's not multiplying the £/hour with the result properly now (giving £1.92 for 7.25 hours when it's multiplying by £6.35)
I used the formula as '=E59-D59+(D59>E59)' where D59 is start time (22:55) and E59 is end time (6:20am).
Am I doing something wrong here?
Cheers
Here's an empty template I made, it has my formulas in it, if you could just edit the WEEK 1 grid as an example would be great, thanks!
Try this in F15
and then copy down.
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
No, newdoverman neglected that you are multiplying by 24 to get hours. If you take his formula and multiply by 24Originally Posted by gps08
Formula:Please Login or Register to view this content.
it will work.
Mine uses a different technique to produce the same result, and sktneer's uses yet a third technique to produce the same result, and the *24 are taking into account in both.
Thanks, both formulas work if I format the cell as number 1234,12 instead of h:mm.
When Start Time in D15 is 22:55 and Finish Time in E15 is 6:20 then the hours should be 7.25. So I think in F15, the formula should be like this.....
I have populated the values in Week1 grid.Please Login or Register to view this content.
This is what I was doing and gave the formula for.
=B1-A1+(A1>B1) all times formatted as h:mm
@ newdoverman
What if you have to multiply the hours so obtained with the wages per hour?
@ sktneer
Start End Hours Rate/Hour Total =B2-A2+(A2>B2) =C2*E2*24
@newdoverman
hmmm.
That works just fine as long as long as gps08 doesn't mind seeing the hours in h:mm format, rather than a decimal number of hours as specified in the first post.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks