Calculating Time Past Midnight Using MOD Function & 24hr / military Clock

[ladypickers]

Hi I am new to the forum and having be scouring the web for a solution to this all day.

I am trying to calculate rest period between the end of previous day and start of the next. I can find plenty of solutions (such as MOD) if you use 12hr clock am/pm, but cannot find a solution using the 24hr clock.

Also if the rest is less than 11hrs but obviously more than 0hrs it needs to generate an hourly overtime penalty. Times are entered as custom hh:mm. I need to return the answer both in time (custom hh:mm) and also in decimals.

The MOD functions does not work if you use 24 hr / military clock, as it returns 17:30 instead of 06:30 between finish and start. The rest of the work sheet is made up of time (hh:mm) and decimals so don't really want to change the format to AM /PM

Time Out / Time In / Rest Period (Time) / Rest Period (Decimal) / Penalty (Time) / Penalty (Decimals)
(A1) / (B1) / (C1) / (D1) / (E1) / (F1)

06:00 / 06:30 / 6.50 / 4:00 / 4.50


I am probably just missing something here, any suggestions would be appreciated

[XLent]

Can you post a sample file to better illustrate -- click Go Advanced when replying and use the Manage Attachments link -- unfortunately the paper clip icon doesn't work on this board.

Time, in XL, is just a decimal value - i.e. 12 hrs is 0.5 -- the AM/PM piece is merely a formatting construct - i.e. 06:00 AM is 0.25 and 06:00 PM is 0.75 -- as is 18:00

MOD looks at the underlying decimal, it doesn't care about how that value has been formatted for aesthetics.

[ladypickers]

Thanks for the reply. I've realised what I'd done re MOD function which is now working but I have another issue - if someone is
sick or on a day off the previous day and therefore cell (C14)is blank the linked cell (E20) should also return
blank but instead is returning 00:00 and therefore F20 G20 & believes its midnight whereas those cells should return zero

Re columns F & G, anything over 11hrs will generate an incorrect result in columns H & I as columns H & I need to
return a result of any hours less than 11. So for instance based on the hours Ive entered H5 should
return 4:00, I4 4.00, H8 should return 0:00, I8 0.00

I hope this makes sense

[XLent]

Whilst I think the calcs will be easy enough it would help if you could advise the expected results, in full, for rows 14 & 20, specifically

i.e. you have advised the impact on rest period for row 20 but not the penalty implication, and it is unclear what, if anything, should be calculated on row 14 itself.

[ladypickers]

Sorry I should have made it a bit clearer it is part of a much bigger spreadsheet. I've updated it everything in yellow are manual entries to show what I need to achieve

If someone is off sick, on a day off or the timesheet hasn't yet been completed for that day of the week there would be no curtailed rest penalty

You may wonder why I am showing column in both time and decimals, it varies from job to job, so I hide one or the other

Currently a blank cell such as E20 was becoming midnight (instead of blank) which excel then thinks the person worked until midnight rather than they not worked, which means F20 & G20 were returning 6 hrs which is incorrect, where as it should be 0 hrs or blank

This is what I need to achieve

Column F needs to return blank ideally if its linked cell in Column C has a blank

Columns F & G to return 00:00 & 0.00 or respectively or ideally blank if either the previous day end time (Column E) or start time (column F) is empty. Also if the rest in column F & G is 11hrs or more then columns H-K need to return blank or 00:00, 0.00.

Also in column F & G I might need to add a formula to each that to round those to the nearest 15 mins (forwards) so for instance 07:40 would become 07:45 & 7.67 would become 7.75, what formula would I need to add if I had to do this later on

Columns H & I need to calculate any hours less than 11 hours. So for instance in F21 is 07:30 hrs G21 is 7.5, therefore H21 needs to be 03:30 & I21 should return 3.5, which has to rounded up to 04:00 & 4.0.

I also then need to do an alternative version for this (columns J & K) that calculated the penalty in 30 min increments for the first hour only and then hourly thereafter (shown in row 12 in orange).

thanks very much

[XLent]

see if the attached is doing what you want - I believe mirrored expected with exception of one row, which I believe should be blank on grounds there was no end time entered for previous day.

in respect of below:

Also in column F & G I might need to add a formula to each that to round those to the nearest 15 mins (forwards) so for instance 07:40 would become 07:45 & 7.67 would become 7.75, what formula would I need to add if I had to do this later on

you can use CEILING to do this, e.g.

Please Login or Register  to view this content.

will round up to nearest 15 min interval -- this concept is actually used in the sample file given your expected results for penalty time.

[ladypickers]

Xlent, thanks you so much, most of it is fine

There is a problem in that if there are 11 hours entered in column f & G then it is returning 1hr in H & I and 30min in J & K whereas it should be 0 in H to K, as the penalty payment only relates to missed rest under 11hrs so 11 and above should return 0 in columns F & G. Ive tested in cell C5 I entered 21:00, & 08:00 (B11), which returns 21:00 (E11), and then 11:00 (F11), 11.00 (G11), H to K are not returning 0:00 / 0.00

There is another problem for column F
=IF(OR($E11="",$D11<>""),"",MOD(B11-E11,1)) includes column D which isnt needed
should it therefore be
=IF(OR($E11="",),"",MOD(B11-E11,1))

If columns H-K needed to return 00:00 instead of empty what would need to be changed is

would column f be
=IF($F14="","00:00",CEILING(MAX(0,11-F14*24),1)/24)

instead of
=IF($F13="","",CEILING(MAX(0,11-F13*24),1)/24)

and would column J be
=IFERROR(CEILING(MAX(0,11/24-F14),IF(HOUR(H14)=1,"00:30","01:00")),"00:00")

instead of
=IFERROR(CEILING(MAX(0,11/24-F13),IF(HOUR(H13)=1,"00:30","01:00")),"")

where would you drop the =CEILING(value,"00:15") into the below.

=IF(OR($E19="",$D19<>""),"",MOD(B19-E19,1))

=IFERROR(F19*24,"")

[XLent]

Ive tested in cell C5 I entered 21:00, & 08:00 (B11), which returns 21:00 (E11), and then 11:00 (F11), 11.00 (G11), H to K are not returning 0:00 / 0.00

I should have added an earlier ROUND to remove very small decimals -- the result of the existing calc, devoid of round, would be 1.77635683940025E-15 which then gets rounded up to 1 (as not exactly 0).

Please Login or Register  to view this content.

=IF(OR($E11="",$D11<>""),"",MOD(B11-E11,1)) includes column D which isnt needed

Col D was deliberately referenced to isolate the "sick" record on grounds these records were "void" in terms of resulting calcs on same row.
If you want to remove remove the OR, and test only the content of E

Please Login or Register  to view this content.

however, as noted, you may in turn get results in that row that you don't really want - i.e. you have no start time to calculate rest period etc...

If columns H-K needed to return 00:00 instead of empty what would need to be changed ...

replace the null string "" with 0

Please Login or Register  to view this content.

note:

you don't need to modify Col I as it is dependent on Col H so when you change Col H the output in Col I will update automatically.

the above principle holds true for Col J, and K -- i.e. replace the null string in Col J formula with 0 (rather than "00:00"), and K will follow.

where would you drop the =CEILING(value,"00:15") into the below.

Please Login or Register  to view this content.

per same logic outlined earlier, Col G can be left as-is given this works off the result of the above calc, so when the output in F changes G will follow.

[ladypickers]

Thanks for the reply and I really appreciate the help you are giving

Column D wasnt part of my original spread sheet it was just in there for you giving a reason for why I had left it blank.

=IF(OR($E19="",$D19<>""),"",CEILING(MOD(B19-E19,1),"00:15")) this appears to be working (but
needs to be without column D included)

=IF($E14="","",MOD(B14-E14,1)) doesn't seem to work once column D is removed.

=IF($F5="",0,CEILING(ROUND(MAX(0,11-F5*24),2),1)/24) this looks as if it working but then how do i do the column J version

=IF($F5="","",CEILING(ROUND(MAX(0,11-F5*24),2),1)/24) this looks as if it working but again how do i do the column J version

I hope this is making sense, I've uploaded the spreadsheet with you charges so you can seen

[ladypickers]

I just posted this but it appears to have gone missing and not posted

Thank you Xlent for all you help, the formulae are all working now

I have one final issue in the sheet that I need to add a round function (if that is the right one to use) to the following.

I need the following to round to the nearest 30 mins or 15 mins (which I will change the formula depending on the job) So for example

06:50 will round back to either 06:30 (if to the nearest 30 mins), or 06:45 (if to the nearest 15 mins).

For some reason i cannot get this to work

Time
=IF(OR($G10="",$K10=""),"",IF((H10-G10)<0,0,(H10-G10)))

Decimal
=IF(OR($G10="",$K10=""),"",IF(((H10-G10)*24)<0,0,((H10-G10)*24)))

[XLent]

If you want to round down, you mentioned 06:50 going to 06:30 for 30 mins rather than 07:00, use FLOOR, i.e.

Please Login or Register  to view this content.

If you want nearest multiple, i.e. return 07:00 for 06:50 rather than 06:30, use MROUND:

Please Login or Register  to view this content.

In terms of:

For some reason i cannot get this to work

Time
=IF(OR($G10="",$K10=""),"",IF((H10-G10)<0,0,(H10-G10)))

Decimal
=IF(OR($G10="",$K10=""),"",IF(((H10-G10)*24)<0,0,((H10-G10)*24)))

For the Time use:

Please Login or Register  to view this content.

For the decimal, per prior note, just multiply the above by 24 -- no need to redo the difference calc, so if above was in I10 for ex. (I don't know which cell it is)

Please Login or Register  to view this content.

[ladypickers]

sorry I see my message wasnt very clear, both the formula below I was using were working fine, but couldn't get them to work with rounding formula, probably as I am not adding it correctly to the formulae below so depending on whether I use the existing formula or yours where would either FLOOR / MROUND / CEILING go in these below, assuming ceiling is the opposite to floor.


Time (Cell is R10)
=IF(OR($G10="",$K10=""),"",IF((H10-G10)<0,0,(H10-G10)))

Decimal (Cell is R11)
=IF(OR($G10="",$K10=""),"",IF(((H10-G10)*24)<0,0,((H10-G10)*24)))

Yours Version Time (Cell R10)
=IF(OR($G10="",$K10=""),"",MAX(0,H10-G10))

Yours Decimal (Cell R11)
=IF(OR($G10="",$K10=""),"",IFERROR(R10*24,0))

[XLent]

Wrap the CEILING / FLOOR / MROUND function around your "result" calculation and, yes, FLOOR is the opposite to CEILING.

I would suggest you make a point of referencing likes of below to better understand which functions are available, what they do, and how to use them.

https://www.techonthenet.com/excel/index.php

[ladypickers]

IHi Xlent I had sorted it out earlier, must have had a senior moment last night, been looking at it for too long

Thanks for all your help on this will mark it as solved.