Hello guys,
This is my first post, so sorry in advance if something is not correct
I'm creating an excel where I keep track of the hours I have worked in a specific month, I also have to keep track of how many of those hours are considered as being day and night hours (night hours go from 21:00 to 06:00 (PM and AM respectively))
At great pain, I've come with this formula to calculate how many hours of the work journey that are considered as night
Cells F3 and G3 are formatted as Time.
If I work from 23:00 to 08:00 it shows a 9 hour working journey and 7 of those hours considered as night. (To calculate the hours that are considered as day, I simply subtract the night hours to the total amount of the work journey).
If the day is OFF, it doesn't calculate anything.
But... there was a day where I had to work more than once. From 03:00 to 04:00 (AM both) and from 21:00 to midnight. The sum should show 4 hours. I edited the formula to accommodate both hours like this
But the thing is that I have to do copy and paste this formula every time I work moth than once in the same day, so what I'm asking you guys is if you guys can help me reformulate this formula in order to automatically do the calculation if 3 conditions are met.
1st - If the 1st cell (F3) is "OFF", it doesn't do anything
2nd - If cells H3 and I3 (or as long as one is empty) are empty is calculates only the values in F3 and G3.
3rd - If there are values in all cells (F3 to I3) it should calculate all cells.
Ps.: Some things are in my language which is Portuguese, so you many not understand everything but what's important, you will understand
This is my first post, so sorry in advance if something is not correct
I'm creating an excel where I keep track of the hours I have worked in a specific month, I also have to keep track of how many of those hours are considered as being day and night hours (night hours go from 21:00 to 06:00 (PM and AM respectively))
At great pain, I've come with this formula to calculate how many hours of the work journey that are considered as night
Excel Formula:
=IF(F3="OFF";"";MOD(G3-F3;1)*24-(G3<F3)*(21-6)-MEDIAN(G3*24;6;21)+MEDIAN(F3*24;6;21))If I work from 23:00 to 08:00 it shows a 9 hour working journey and 7 of those hours considered as night. (To calculate the hours that are considered as day, I simply subtract the night hours to the total amount of the work journey).
If the day is OFF, it doesn't calculate anything.
But... there was a day where I had to work more than once. From 03:00 to 04:00 (AM both) and from 21:00 to midnight. The sum should show 4 hours. I edited the formula to accommodate both hours like this
Excel Formula:
=IF(F5="OFF";"";MOD(G5-F5;1)*24-(G5<F5)*(21-6)-MEDIAN(G5*24;6;21)+MEDIAN(F5*24;6;21))+IF(H5="";"";MOD(I5-H5;1)*24-(I5<H5)*(21-6)-MEDIAN(I5*24;6;21)+MEDIAN(H5*24;6;21))But the thing is that I have to do copy and paste this formula every time I work moth than once in the same day, so what I'm asking you guys is if you guys can help me reformulate this formula in order to automatically do the calculation if 3 conditions are met.
1st - If the 1st cell (F3) is "OFF", it doesn't do anything
2nd - If cells H3 and I3 (or as long as one is empty) are empty is calculates only the values in F3 and G3.
3rd - If there are values in all cells (F3 to I3) it should calculate all cells.
Ps.: Some things are in my language which is Portuguese, so you many not understand everything but what's important, you will understand
