Forum Discussion
Time format
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.)
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.)
