You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Hi,
I am trying to create a table that calculates my work schedules and tells me how much I have to charge.
I want to use just 3 input cells (time of entry, break hours, and leaving time), and with those 3 cells, it should work.
The thing is that during the normal time (from 08:00 to 22:00) I charge, for example, 10€/h; from 22:00 to 00:00, 20€/h; and from 00:00 onwards, 30€/h.
Any help?
Thanks.
Hi Dani,
I'd take a closer look at your formula, but I dread the idea of having to type the whole thing into a cell or onto a blank page to make it easier to separate into more easily analyzed parts.
Please:
Then paste the copied formula (as text) into a reply to this post.
Regards,
Barry
Hi Danval31,
I don't have a solution, but here are my thoughts to stimulate some discussion. Perhaps other users can add their thoughts. Sorry for such a long reply.
Table 1 has one Header Row.
Columns A, B, C and D are where you enter data.
Column A "Job" is for your use. No formulas refer to Column A.
Columns B and C are formatted as Date & Time (24 hour clock) like this:
Enter only a time, such as 9:00 and Numbers will automatically insert the Date on the day when you made that entry.
For example, in Row 4, you started a job on 13 Sep finished the job on 14 Sep. The times have "crossed midnight" but if you enter the time of entry on the day when you started the job and enter the time of leave on the day when you finished the job, Numbers will insert the correct Date.
Column D is formatted as Duration like this:
Enter the Duration of your breaks as 1h0m or 1h30m for example.
Now we need to calculate hours worked within each time bracket. That is an interesting challenge!
By the way, how do you deduct break times within each time bracket?
Over to you, Numbers gurus!
Regards,
Ian.
Hi again Dani,
We haven't forgotten you!
Instead of a monster formula, I tried breaking the calculation into small steps by adding extra columns. You can hide those extra columns.
Formula in Table 1 G2: IF(AND($E2≥Lookup::$B$2,$F2≤Lookup::$B$3),($F2−$E2)×24,0)
The formula works in Row 2, but I am having trouble with midnight (0:00). Or should that be 24:00 ?
Also, the trouble with the formula seems to be with ≥ and/or ≤ or maybe because TIMEVALUE ignores the date.
We need a formula that will fill across and down.
Suggestion: maybe another user can come up with a formula that adds one minute to (or subtracts one minute from) either Entry or Leave times?
Come on, Numbers gurus!
Regards,
Ian.
Thanks for your answer, Ian.
That's exactly the way I did it, but I didn't know how to continue.
Regarding your final question, break time is always 10€/h, as if it was in the first time bracket, and I used to calculate the total amount of hours with this formula:
DUR2HOURS(C2−B2−D2)
C2 is the leave time
B2 is the entry time
D2 is the break hours
Thanks,
Dani
HI Danval,
I think Yellowbox has i pretty much solved, and has also noticed the point where there' a lack of information needed for a correct calculation.
You wrote: "I want to use just 3 input cells (time of entry, break hours, and leaving time), and with those 3 cells, it should work."
What is the schedule and pay situation with respect to "break hours"?
Does the break always occur after a specific number of hours consecutive work time?
Is the break period mandated and paid time with the regular rate for the time of day it is taken, or is it voluntary, with time and duration chosen by the worker, and taken at no cost to the employer? Or is break-time pay more complicated than either of the two extreme cases?
Regards,
Barry
Ahh…
Should have read on. I see you've already answered that.
"break time is always 10€/h, as if it was in the first time bracket"
That adds significant complication to the calculation.
If the break is taken during the first time bracket, it has no effect on the total pay.
If it is taken entirely in the second time bracket, your earnings are reduced by 10€ x the length in hours of the break.
If it is taken entirely in the third time bracket, your earnings are reduced by 20€ x the length of the break.
("Reductions" are compared to what you would have earned had you been working during the break time.)
The calculations get a bit more complex if the break starts in on time bracket and ends in another.
I think you are going to need four data input columns for this:
Entry (date and) time,
Break start (date and) time,
Break end (date and) time,
Leave (date and) time.
Regards,
Barry
Hi Dani,
Danval31 wrote:
break time is always 10€/h, as if it was in the first time bracket
I interpret that differently from Barry. Do you mean that you deduct 10€/h for breaks, no matter when the breaks occur?
Regards,
Ian.
Hi again,
Idk if this will help, but I used to do this exact same thing but with just 2 time brackets, not 3, using this monster formula:
DUR2HOURS(IF(OR(ISBLANK(B2);ISBLANK(C2));DURATION(0);((C2−B2−D2)×10)+DURATION(0;0;IF((IF(TIMEVALUE(C2)≥TIMEVALUE(TIME(22;0;0));(TIMEVALUE(C2)−TIMEVALUE(TIME(22;0;0)))×24;IF(TIMEVALUE(C2)<TIMEVALUE(TIME(8;0;0));(TIMEVALUE(C22)−TIMEVALUE(TIME(0;0;0))+TIMEVALUE(TIME(2;0;0)))×24;0))−IF(TIMEVALUE(B2)≥TIMEVALUE(TIME(22;0;0));(TIMEVALUE(B2)−TIMEVALUE(TIME(22;0;0)))×24;IF(TIMEVALUE(B2)<TIMEVALUE(TIME(8;0;0));(TIMEVALUE(B2)−TIMEVALUE(TIME(0;0;0))+TIMEVALUE(TIME(2;0;0)))×24;0)))<0;0;IF(TIMEVALUE(C2)≥TIMEVALUE(TIME(22;0;0));(TIMEVALUE(C2)−TIMEVALUE(TIME(22;0;0)))×24;IF(TIMEVALUE(C2)<TIMEVALUE(TIME(8;0;0));(TIMEVALUE(C2)−TIMEVALUE(TIME(0;0;0))+TIMEVALUE(TIME(2;0;0)))×24;0))−IF(TIMEVALUE(B2)≥TIMEVALUE(TIME(22;0;0));(TIMEVALUE(B2)−TIMEVALUE(TIME(22;0;0)))×24;IF(TIMEVALUE(B2)<TIMEVALUE(TIME(8;0;0));(TIMEVALUE(B2)−TIMEVALUE(TIME(0;0;0))+TIMEVALUE(TIME(2;0;0)))×24;0)));0;0;0)×10))
Thanks,
Dani
Hi!!
After all these days I've finally found a way of doing it! It is not elegant nor easy, but it does its job. Let me know what you think.
Thank you for your help!!
Dani
Here you've got the formulas:
10€/h: IFS(AND(TIMEVALUE(E2)>TIMEVALUE("08:00");TIMEVALUE(E2)≤TIMEVALUE("22:00"));E2−C2−D2;AND(OR(TIMEVALUE(E2)>TIMEVALUE("22:00");TIMEVALUE(E2)=TIMEVALUE("00:00"));TIMEVALUE(C2)<TIMEVALUE("22:00"));(TIMEVALUE("22:00")−TIMEVALUE(C2))×24−DUR2HOURS(D2);AND(OR(TIMEVALUE(E2)>TIMEVALUE("22:00");TIMEVALUE(E2)=TIMEVALUE("00:00"));TIMEVALUE(C2)≥TIMEVALUE("22:00"));0;AND(TIMEVALUE(E2)>TIMEVALUE("00:00");TIMEVALUE(E2)≤TIMEVALUE("8:00");TIMEVALUE(C2)≥TIMEVALUE("8:00");TIMEVALUE(C2)<TIMEVALUE("22:00"));(TIMEVALUE("22:00")−TIMEVALUE(C2))×24−DUR2HOURS(D2);AND(TIMEVALUE(E2)>TIMEVALUE("00:00");TIMEVALUE(E2)≤TIMEVALUE("8:00");OR(TIMEVALUE(C2)≥TIMEVALUE("22:00");TIMEVALUE(C2)<TIMEVALUE("8:00")));0)
20€/h: IFS(AND(TIMEVALUE(E2)≤TIMEVALUE("22:00");TIMEVALUE(E2)>TIMEVALUE("8:00"));0;AND(TIMEVALUE(E2)>TIMEVALUE("22:00");TIMEVALUE(C2)<TIMEVALUE("22:00"));(TIMEVALUE(E2)−TIMEVALUE("22:00"))×24;AND(TIMEVALUE(E2)=TIMEVALUE("00:00");TIMEVALUE(C2)<TIMEVALUE("22:00"));2;AND(TIMEVALUE(E2)>TIMEVALUE("22:00");TIMEVALUE(C2)≥TIMEVALUE("22:00"));E2−C2;AND(TIMEVALUE(E2)=TIMEVALUE("00:00");TIMEVALUE(C2)≥TIMEVALUE("22:00"));ROUND(24−TIMEVALUE(C2)×24;1);AND(TIMEVALUE(E2)≤TIMEVALUE("8:00");TIMEVALUE(C2)<TIMEVALUE("22:00");TIMEVALUE(C2)≥TIMEVALUE("8:00"));2;AND(TIMEVALUE(E2)≤TIMEVALUE("8:00");TIMEVALUE(C2)<TIMEVALUE("8:00"));E2−C2;AND(TIMEVALUE(E2)≤TIMEVALUE("8:00");TIMEVALUE(C2)≥TIMEVALUE("22:00"));ROUND(24−TIMEVALUE(C2)×24;1))
30€/h: IFS(TIMEVALUE(E2)>TIMEVALUE("8:00");0;AND(TIMEVALUE(E2)≤TIMEVALUE("8:00");TIMEVALUE(C2)<TIMEVALUE("8:00"));E2−C2;AND(TIMEVALUE(E2)≤TIMEVALUE("8:00");TIMEVALUE(C2)≥TIMEVALUE("8:00"));TIMEVALUE(E2)×24)
I also send a Google Drive link to download the .numbers file: https://drive.google.com/file/d/19nIWFG5aeT2_JtFNroCSrBUqNpX8G4xY/view?usp=sharing
Thank you!!
Dani
Exactly, Ian got it right.
It is just a 10€/h multiplier. It doesn't matter when it is, it is always 10€/h.
Thanks for your help,
Dani
Work schedules calculation?