Hi all.
Please can someone help me with a formula that can work out the time I’ve worked Before 6am, between 6am-6pm and after 6pm.
I’ve added a link to the sheet here.
https://www.icloud.com/numbers/08h_26OINHXAJAHD1JMFBPYLA#Blank
Formula required in columns H and J. I’ve added these in manually. I don’t want to use the date and time format.
Thanks DaCord
MacBook Pro 13", macOS 10.14
Alright, so I’ve got your formulas in place. Here they are:
Let’s look at Column J first. What this function does is it first checks to see if your start time is before 6:00. If so, it subtracts your start time from 6:00 to determine how many hours were worked before 6:00. If your shift started after 6:00, a value of 0 is given. Then it checks if your shift ended after 18:00. If so, it subtracts 18:00 from your end time to determine how many hours were worked after 18:00. If it ended before 18:00, a value of 0 is given. These two values are added together. This is all enclosed in a DUR2HOURS function to give the number of hours in numerical format as you requested. Column H simply subtracts the time from column J from the length of your shift (column C) to find the time remaining, as J + H = C.
If this is not to your satisfaction, let me know and I can take another look. I notice that my column H values align with your manually calculated values, but column J does not. As the difference is not equal to D, and I have derived H from J, I’m puzzled why there would be such a discrepancy. I’m not going to say your math is wrong, but I will say I am unclear on what method you used to arrive at those figures, so I hope my formulas are acceptable.
One final note: the accuracy of these formulas is conditional upon your shifts always starting after midnight and ending before. That is to say, it assumes you are never on shift at midnight. If this is not the case, it complicates matters further. It can still be done, but an additional logic function would be needed to check for this, and adjustments made to the calculation if true. So let me know.
Check out the SG3 tab I added:
The basic problem (a hard one) of splitting your time into shifts is, I believe, solved here. You should be able to easily incorporate that into your document and take the decimal hours for each shift and multiply by your relevant rates.
Entering a point in time as a duration is going to cause confusion and problems. Time to fix that! The TIMEVALUE approach that I use doesn't care about the date. But it does care about the time, expressed either as a true date-time in the cell or as a "time string" (formatted with the colon so Numbers recognizes it as a time).
SG
Upon review, the H formula was needlessly complicated, owing to an adjustment I made to an earlier, incorrect formula. The simplified formula is:
H2 = DUR2HOURS(C2−DURATION(0,0,J2,0,0,0))
This has already been adjusted in your file.
Hi SG
If you take a look at sheets 1, 2, 3, worksheet and yours. Non come to the correct totals. Therefore how is it possible for me to except any of the solutions provided. We have being working through this and there have been many comments added to each sheet.
Lets take a look at the last row in your sheet SG3.
Start time 04:45 and finish time 21:30 total of 6:45 of work.
Column D=3.25 correct and E=3.50 incorrect. E should be 4.75 because of the guaranteed number of hour (8hr or 12hrs) in a day. The short fall gets added to the finish time.
I explained this in sheet 1 with the added comments below Bismarck attempt.
I don’t want to get into any arguments with anyone and I’m happy to use your solution if it means I have to add two extra columns to convert the duration in my sheet to come to the correct totals.
It’s up to you, you are the expert at this not me. I would appreciate your help but understand if you don’t.
As explained in the beginning of the post I need this to sort out my pay which is always incorrect from the agency I work for.
Regards
DaCord
DaCord wrote:
Column D=3.25 correct and E=3.50 incorrect. E should be 4.75 because of the guaranteed number of hour (8hr or 12hrs) in a day. The short fall gets added to the finish time.
If you have a guaranteed minimum then you would reflect that in the 'Total Hours' column.
I've changed the formula in column F, highlighted in red:
=MAX(8,MOD(TIMEVALUE(C2)−TIMEVALUE(B2),1)×24)
The MAX simply says take the larger of 8 or the calculated total. If 12 hours you would change the 8 to 12.
Not sure I understand the logic behind your row 3 and 4, though. In row 3 you show 0 in the 06:00 to 18:00 shift. But you're working to 07:00, i.e. past 06:00, so some of the time is clearly in that 06:00 to 18:00 shift. Perhaps some special rules apply.
Anyway, the MAX may give you some ideas. And if you're crossing midnight I am pretty confident in the advantages of the TIMEVALUE approach.
SG
Is there a reason why you are using duration for dates and times? If you can tell us why you refuse to use date/time format, we may be able to suggest ways to make that format work for you, which would allow you to do what you are trying to do quite easily. As things stand, I’m reluctant to work out some complex workaround to help you do this the wrong way.
EDIT: It actually would not be all that complicated. Let me see what I can do. But my point above still stands.
Hi Bismarck, thank you for the reply to my post. Its not that I am against date and time format, its because my worksheet is +/- 5 years in the making and it would be seriously difficult for me to start remodelling it to suit this requirement.
The reason I require this formula is that I have recently moved over to an agency that cannot seem to pay me correctly and I'm always having to argue my case that they have underpaid me which is very frustrating.
If I start with sheet 1 and look at your formula added in column J, there are incorrect totals. Let me try explain.
Row 2
Row 6
Sheet 2
Much appreciation for your time and look forward to your reply.
Regards
DaCord
My apologies, I was in a hurry and did not thoroughly test my formulas, as you said. I can correct them. Which version would you like me to correct? I can work on Sheet 1, fixing my H and J formulas without touching anything else, or I can correct the errors in Sheet 2 allowing you to do that. I can also make modifications to Sheet 2 to make it usable for me if you have something in mind, but I won’t be offended if you simply reject Sheet 2. I enjoyed doing it and it required me to come up with solutions to problems I had not encountered before so it was a learning experience for me. Thanks for letting me tinker with it.
Bismarck please tinker as much as you like. At the end of the day I’ll use the best solution either form sheets 1, 2, 3 and SG recently add solution.
There is another issue I need to deal with and that is adding a 12hrs guaranteed shift. If you look at my sheet 3 I’ve added a column V, Guaranteed Hours and tweaked the J:82 cell formula.
I’ve added other comments in the tables and cells and a Chat sheets for other comments.
In reply to your comments made in the chat sheet
“That brings me to your breaks. I want to be sure I am clear on those. The first 45 minutes is unpaid? If you take a 1 hour break, only 45 minutes are deducted from your paid hours? And no deduction is made if your guaranteed hours are not met that shift? And finally, the break is deducted from the end of your shift, and therefore the nighttime rate will generally apply to the deductiond?”
Comments:
The above paragraph is the correct analogy.
Only one thing missing from that would be if a 30 min break has been taken, only 30 min is deducted after 8h 30m because of the guaranteed 8hrs.
I’ve noticed there are errors everywhere, I gather once you have sorted the break formula these can be addressed.
Regards
DaCord
SG,
I'm not quite sure if you read the comment that Bismarck put into the chat sheet. Which I have replied to and it is common decency to carry out what you have said you were going to do. He was working on the worksheet he created and has not completed it. There are loads of errors and he was going to complete this last week. There are formulas he put in and I cannot read his mind to finish off what he had started.
I don't particularly want to use your solution because it means I have to change the duration input I use to time value input you are suggesting above which I have explained to you on numerous occasions before that it will impact the way everything else works in my file.
I don't want pointers, I want help and that's what this site is all about. Helping people. If I could do it myself then I would with a duration solution and not time values.
It's entirely up to you if you want to help further, no hard feelings if you don't.
Thanks DaCord
Lots of time spent for you and loads of help given here in this thread (way beyond the call of duty in my opinion) and you don’t seem to find any of it helpful or want to incorporate any of the advice given, even basic things like when to use date-time and when to use duration.
Could it be that you really need to find a personal consultant somewhere to do the work for you?
SG
It looks like I have managed to solved the above question which was a painstaking challenge.
I have continued to use the DURATION function and there seems to be no issue with the cross over from one day to the other and hopefully I am not faced with any further challenges, if so, I'll have to make that my next challenge.
Here is what I've come up with in Sheet 3 T1 and it seems to be working
Added new columns K, L, M, N and updated column W formula.
I did quite a bit the other day and intended to finish up today. Where I left it, I was nearly there. The rest was essentially just cleanup, consolidating calculation columns, removing temporary columns used to perform checks to ensure data was accurate, and things of that nature. Do you still want me to keep going?
With regards to your issue of different time periods being at different rates, because your start and end times are always rounded to a quarter hour, I was able to split the day into 96 intervals of 15 minutes each, and create a data table with 96 rows that contains the starting time period (day/night) of each one and the number of intervals until the time period changes. Then, using the start time and duration of your shift, it is possible to determine how many daytime and nighttime intervals your shift covers. From there, calculating your pay is very simple, and that is where I left off when I last worked on it. Did you keep this solution or go in a different direction?
I have not selected solved on what I have done yet as I’m not 100% certain that my formulas are correct. They seem to be working at the moment but there is not enough data to justify what I have done.
I would really appreciate it if you would please complete what you have started and potentially we could have two solutions.
I think it would be also important to complete this for the interest of all the people that have viewed the post.
Thank you very much once again.
DUR2HOURS BEFORE, BETWEEN and AFTER Start and Finish Times of shift.
