Time format

[vb_pilgrim]

why does the cell turn my time to 12:00 AM regardless of the number or formula in the cell?  When I expand the cell, I see the date is some day in January 1900.  My computer (Dell Inspiron, Windows 10) is set to the correct date.  I just want to turn the result of the formula, 19, to read 7 PM. 

Thank you in advance

[#2]

Hi @vb_pilgrim,

MS and Excel are starting when counting in dates with the 1.1.1900. For example today is day 44354. You can change this when you just change the formatting from date to for example default

[#3 · Accepted solution]

@vb_pilgrim 

To convert 19 to a valid time, use a formula to divide it by the number of hours in a day, 24.

So with 19 in for example A2, enter the following formula in B2:

=A2/24

and format B2 as a time.

(Some background:

Excel stores dates and times as numbers, with 1 day as unit.

January 1, 1900 is stored as 1; January 2, 1900 is stored as 2, etc., and June 7, 2021 is 44354.

Times are fractions of a day: 6 hours is 0.25, 18 hours is 0.75 etc.

For a date and time, Excel adds the whole number and the fraction: 6 PM on June 7, 2021 is 44354.75.

But if you only specify the time, without a date, the integer part of the number is 0, corresponding to the non-existent date January 0, 1900.)

[#4]

@Hans Vogelaar 

Thank you.  This helped a lot.

Vicki