Date and Time formatting issues

New Contributor


I am having trouble getting the Date and Time to show correctly in the table view, it is only showing in the formula bar. So, when my macro grabs the data from that cell it looks like 0.22869212962963 instead of 05:29:19.

Please help!



3 Replies


Apply a date format to D6, for example d-mmm-yyy, or whatever you like.

Apply a time format to E6, for example hh:mm:ss.


(Explanation: Excel stores dates and times as numbers, with a unit of 1 day. For example, 6:00 AM is stored as 6/24 = 0.25.

How do I do this? This is not something I've had to do before


Select D6.

On the Home tab of the ribbon, in the Number group, click on the drop-down arrow in the Number Format box, and select Short Date (or Long Date if you prefer).


Select E6.

This time, select Time from the Number Format drop-down.