I need help with a formula, I want to put a start time and finish time into two cells and the answer needs to be to the nearest 15 minutes. ie 08.05 would be 0800, 07.25 would be 07.30 etc..


I have managed to find this that does the basic bit but not the rounding out.

=IF(C1<B1,((C1-B1)*24)+24,(C1-B1)*24)

but I find to put time in I need to put colons in and I would prefer to just type 0730 and not 07:30,I thought the time format would allow that but all I get is strange numbers coming up.

any help greatly appreciated

Bill Reed

Long formula but here goes:
suppose start time is in A1 and end time in B1 entered in military time ( like 0730) then try

=ROUND(MOD(time(left(b1,2),right(b1,2),0)-time(left(a1,2),right(a1,2),0),1)*24/0.25,0)*0.25/24

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi

I take it that you want decimal time! Format B1 & C1 as text, you can then enter "0", ie 0715.

In D1: =MROUND(MOD((--REPLACE(C1,3,0,":"))-(REPLACE(B1,3,0,":")),1),"00:15")*24

and format the cell "General".

Or for proper time

=MROUND(MOD((--REPLACE(C1,3,0,":"))-(REPLACE(B1,3,0,":")),1),"00:15")

and format as time.

Kevin

That works a treat, but how do you get the #Value! to disappear in the answer cell? when there is nothing in B1 and C1.

I am using

=MROUND(MOD((--REPLACE(C1,3,0,":"))-(REPLACE(B1,3,0,":")),1),"00:15")*24

Hi

One way, wrap in IFERROR.

=IFERROR(MROUND(MOD((--REPLACE(C1,3,0,":"))-(REPLACE(B1,3,0,":")),1),"00:15")*24,"")

Kevin

Thanks for all your help on this have got it up and running now. thanks again. Billl