using RIGHT to always get fractional points

I've attached a workbook which contains hourly data in column A. In column F I want to only obtain the timestamp from A if the hour is 23:00.

I've tried using RIGHT to get the last 5 digits from column A but the result is gibberish. Instead of 23:00 I'm getting 33333.


Then I tried ROUNDDOWN column A to 2 digits, then using RIGHT to pick only the hours that have fractional points of .95. This worked well until the date (in Excel format) happened to be 43795. At this point, the method is picking two times on that date rather than one. (you'll see this in the highlighted rows of the attached workbook).

Any advice on how to consistently obtain the correct timestamp of 23:00 every time?

Thanks!

You can use this formula in F2:

=IF(HOUR(A2)=23,INT(A2),"")

then copy down as required. You can see that it doesn't need any of the other columns that you have used.

Hope this helps.

Pete

Thanks. Both solutions work because what we're looking at is an intermediate step. If the hour is not 23:00 then the column to the right can be "" or 0. Unfortunately I've got another prob in the workbook involving VLOOKUP suddenly failing, so I'll create a new post on it soon. Thanks!

Hi Dave,

Column F was formatted as a date, and if it was just the time that was required then you could just return 23, or TIME(23,0,0)

Pete

Yes, it was ambiguous.

Anyway, the OP seems to be saying that we were both right !!

Pete