CODEWITHSUNDEEP
exceltimeexcel-formularounding
Rob3922
Rob3922

Reputation: 13

Calculate the difference and sum of two times and round to the nearest 1/4 hour in decimal format

I am trying to create a simple spreadsheet to accurately calculate the difference between two times, over three separate periods in a day for staff payroll purposes. There is a start and end time for each input to the exact minute in 24 hour time (07:21, 22:42 etc). There is a total column for each of the three periods which needs to SUM the work time, convert it to decimal, and round to the nearest 1/4 hour (i.e: 4.75 hours) in the one formula.

I can achieve one or the other, but cannot get my formulas to work together for some reason. I have tried INT, ROUND, MINUTE etc based on the following:

=((ROUND(C9*96,0)/96)*24)-((ROUND(B9*96,0)/96)*24)

=INT(c9)*24+HOUR(c9)+ROUND(MINUTE(c9)/60,2)-INT(b9)*24+HOUR(b9)+ROUND(MINUTE(b9)/60,2)

And several hundred variations.

I think I am overthinking this one, and not using a correct formula.

Upvotes: 0

Views: 2006

Answers (2)

Tom
Tom

Reputation: 13

I use this:

=ROUND((C9-B9)*96,0)/96*24

Upvotes: 0

aucuparia
aucuparia

Reputation: 2051

MROUND allows you to round to the nearest multiple (which doesn't have to be an integer). Date/time values in Excel are stored as fractional numbers of days, so to convert to hours you can just multiply by 24. Putting those together gives:

=MROUND(24*(C9-B9),0.25)

Upvotes: 2

Related Questions