CODEWITHSUNDEEP
exceldatetime
Allison King
Allison King

Reputation: 13

Excel date formatting convert to number

I have a data file imported where there is a column for the date. It is custom formatted as [-$409]ddmmmyyyy h:mm:ss:000 so that it takes a number like 41769.68 and has it shown in the cell as 10May2014 16:23:04.883, for instance.

I want to be able to take just the hour and use that for conditional formatting though. For example, if the hour is between 7AM and 11AM, I want another column to say 'morning', or something of that sort. I know I can change the format cells option to just 'h' to get the hour, but using that in another block of code still returns the original 41749.68 number rather than 16, for example.

Is there a way to get just the hour to show in its actual number form rather than the whole date, or if not, is there a way to parse the number 41769.68 to find the hour?

Upvotes: 1

Views: 126

Answers (2)

pnuts
pnuts

Reputation: 59495

The hour is .68 (the decimal part) *24 (each day is 1 unit to Excel). If you don't want the minutes then either round or truncate (eg with INT). Either way Conditional Formatting will not allow you to format as 'morning'.

You could try: 

=HOUR(A1)

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96781

With your value in A1, in another cell enter:

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

will display 16

Upvotes: 0

Related Questions