Decimal Time

Convert time to decimals

=HOUR(A1)&"."&SUBSTITUTE(MINUTE(A1)/6,".","")

Or as Asser suggested, just use this and format as General

=24*A1

Or this one from Robert Zimmer

=HOUR(A1)+(MINUTE(A1)/60)

This version of his will work with seconds if formatted as General

=HOUR(A1)+(MINUTE(A1)/60)+(SECOND(A1)/3600)

As will this one

=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))*24

Round time up to 15 minute increments

=VLOOKUP(MINUTE(A1),{0,0;15,0.25;30,0.5;45,0.75},2,1)

Another one of Asser's alternatives

=INT(4*(A1*24-INT(A1*24)))*0.25

This version from Andrew Hunt simply rounds up and down (Make sure the Analysis Tool Pak is installed)

=((HOUR(A1)*60)+MROUND(MINUTE(A1),15))/60

Show time as 1 - 12 regardless of AM or PM

=IF(MOD(A1,12)=0,12,MOD(A1,12))

Yet another version from Asser

=MOD(ROUND(A1*24-1,0),12)+1



© Copyright andrewsexceltips.net All Rights Reserved.