Excel date formatting convert to number -

Excel date formatting convert to number -

i have info file imported there column date. custom formatted [-$409]ddmmmyyyy h:mm:ss:000 takes number 41769.68 , has shown in cell 10may2014 16:23:04.883, instance.

i want able take hr , utilize conditional formatting though. example, if hr between 7am , 11am, want column 'morning', or of sort. know can alter format cells alternative 'h' hour, using in block of code still returns original 41749.68 number rather 16, example. there way hr show in actual number form rather whole date? or if not, there way parse number 41769.68 find hour?

thanks!

the hr .68 (the decimal part) *24 (each day 1 unit excel). if don't want minutes either round or truncate (eg int). either way conditional formatting not allow format 'morning'.

you try:

=hour(a1)

excel date time