End Time - Start

[Duncan Butterworth]

Hi,
I can't accomplish this maybe someone can help.
Thanking you in advance.

Start time End time
07/05/2019 09:00 07/05/2019 13:40 4.75
08/05/2019 08:58 08/05/2019 13:35 4.5
09/05/2019 09:03 09/05/2019 13:43 4.5
10/05/2019 08:57 10/05/2019 13:48 4.75


what I need is a formula that will round these times to 15 mins take the start time from the end time and return as a decimal

Regards,
Duncan


EDIT: Meant to call it End time (rounded to 15mins) minus Start Time (rounded to 15mins as a decimal)

 

[CalcSux78]

MROUND should get you there:

Excel 2010

	A	B	C
Start	End	Decimal

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5/7/2019 9:00[/TD]
[TD="align: right"]5/7/2019 13:40[/TD]
[TD="align: right"]4.75[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5/8/2019 8:58[/TD]
[TD="align: right"]5/8/2019 13:35[/TD]
[TD="align: right"]4.5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5/9/2019 9:03[/TD]
[TD="align: right"]5/9/2019 13:43[/TD]
[TD="align: right"]4.75[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5/10/2019 8:57[/TD]
[TD="align: right"]5/10/2019 13:48[/TD]
[TD="align: right"]4.75[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=(MROUND(B2,15/60/24)-MROUND(A2,15/60/24))*24[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Duncan Butterworth]

Calcsux,
Thanks for the reply I think we are nearly there.

hours Start Time End Time
6.00 07/05/2019 07:36:51 07/05/2019 13:45:49
6.00 08/05/2019 07:33:50 08/05/2019 13:35:47
6.00 09/05/2019 07:39:33 09/05/2019 13:43:35
6.00 10/05/2019 07:35:54 10/05/2019 13:33:36

When I use the formula you gave and compare it to the first column it appears correct but when I conditional format it to turn it red if they are not the same it colours it, I think its the seconds that are creating the problem.
Sorry for the wrong data posted it didn't contain the second.

Duncan

 

[CalcSux78]

Not sure if I follow you on the last part. Would you be able to post your conditional formatting formula?

Also, to clarify... You do want to round the start time and the end time, then take the difference of those rounded times to calculate hours, correct?