Date and Time formatting issues

New Contributor

Hello, 

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!

HurricaneAlaine_0-1656796366462.pngHurricaneAlaine_1-1656796388671.png

 

3 Replies

@HurricaneAlaine 

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

@HurricaneAlaine 

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).

S1543.png

Select E6.

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

S1544.png

Result:

S1545.png