converting minutes since midnight into time format and rounding to the nearest quarter hour

[abberyfarm]

Hi there,

I have minutes since midnight in a column. How could I convert this to time values and round the minutes to the nearest quarter of an hour?

Here is an example

Excel Workbook
	I	J
7	497	08:30:00
8	522	08:45:00
9	365	06:00
Sheet2

Many thanks for you sugggestions

 

[Jon von der Heyden]

497 to the nearest quarter of an hour will be 8:15, not 8:30 (as it is actually 8:17)

=ROUND(A1/24/60/"00:15:00",--"00:15:00")*"00:15:00"

If you want to round up:

=CEILING(A1/24/60,--"00:15:00")

 

[T. Valko]

Hi there,

I have minutes since midnight in a column. How could I convert this to time values and round the minutes to the nearest quarter of an hour?

Here is an example

Excel Workbook
	I	J
7	497	08:30:00
8	522	08:45:00
9	365	06:00
Sheet2

Many thanks for you sugggestions

Like this...

=ROUND(I7/1440*96,0)/96

Format as [h]:mm

I think 8:30 should actually be 8:15.

 

[abberyfarm]

Thanks for the help guys, appreciate it!

 

[T. Valko]

Thanks for the help guys, appreciate it!

You're welcome!