looking to round values up

[greggatz]

I'm creating a spreadsheet to track time in/time out from our lab; the problem I'm running into is when items are submitted prior to breaktime. I need to showcase that this is happening, so I can't round that value up.

So...how can I round the value up in the cell that showcases the total time?

It would be something like this: subtract Time In from Time out, but if Time In is between 11:30 & 12:00, round up Time In to 12:00.

[JohnTopley]

Time in=A2, Time out=B2

=IF(AND(A2>=TIME(11,30,0),A2<=TIME(12,0,0)),B2-TIME(12,0,0),B2-A2)0

[FDibbins]

What you need to understand about dates and times in excel is...

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Tue Jun 2015) is actually 42171

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So, you just need to subtract that 30 mins from the total. 30 mins is 30/60/24

[greggatz]

Thanks you two!

[FDibbins]

Happy to help, thanks for the feedback