Decimal Time

How can you convert in excel decimal time into 12- or 24-hour notation ?

Answer: if you have the number 4.50 (=A1) and you really want 4:30 or 4:30AM use =A1/24 and format as needed. If it should be 17:00 or 5:30PM use =(A1/24)+0.5

How can you convert to decimal time ?

Answer: to get the opposite, that is a decimal time from a true time, use =A1*24 (A1 being the true time format of course in this case)

Now this is simple enough, but why is this correct ?

The answer follows from the fact that Microsoft Excel stores dates as sequential numbers known as serial values and times as decimal fractions because time is considered a portion of a day. This decimal number is a value ranging from 0 to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.). Example: 21:42:10 equals 21/24 + 42/(24*60) + 10/(24*60*60) or 0.90428241. Now if you go back to the true time 4:30, this is stored by excel as 0.1875. If you multiply this by 24, you get 4.5
To display the serial number of a particular time, you can also use the TIME worksheet function if needed.

We need to make two remarks however.

Above explanation is valid for converting time, but not always for converting dates or dates with time! Originally, Excel for Windows was based on the 1900 date system, because it enabled better compatibility with other spreadsheet programs that were designed to run under MS-DOS and Microsoft Windows, and therefore it became the default date system. Originally, Microsoft Excel for the Macintosh was based on the 1904 date system, because it enabled better compatibility with early Macintosh computers that did not support dates before January 2, 1904, and therefore it became the default date system. Or January 1, 1900 has serial value 1 and December 31, 9999 serial value 2958465 if you use the WIndows version. If you use Macintosh, than January 2, 1904 has serial value 1 and December 31, 9999 serial value 2957003. The difference between the two date systems is 1,462 days; that is, the serial value of a date in the 1900 date system is always 1,462 days greater than the serial value of the same date in the 1904 date system. So be careful if you use spreadsheets made on another system ! To solve differences when doing a copy/paste or with external referencing of 2 excel sheets based on a different date system, you need to add/substract 1462 days.

A second issue is the way excel interpretes two-digit years. 00 through 29 is interpreted as the years 2000 through 2029. For example, if you type the date 28/5/19, Excel assumes the date is May 28, 2019. 30 through 99 is interpreted as the years 1930 through 1999. For example, if you type the date 28/5/98, Excel assumes the date is May 28, 1998.
In Microsoft Windows, you can change the way two-digit years are interpreted for all Microsoft Windows programs that you have installed (or you can of course just type the correct 4-digit year):

1. Click the Start button, and then click Control Panel.
2. In Windows Vista, click Clock, Language, and Region. In Windows XP, click Date, Time, Language, and Regional Options.
3. Click Regional and Language Options.
4. In Windows Vista, in the Regional and Language Options dialog box, click the Formats tab. In Windows XP, in the Regional and Language Options dialog box, click the Regional Options tab.
5. In Windows Vista, click Customize this format. In Windows XP, click Customize.
6. Click the Date tab.
7. In the When a two-digit year is entered, interpret it as a year between box, change the upper limit for the century. As you change the upper-limit year, the lower-limit year automatically changes.

Update: perhaps I will post some more of these simple tutorials in the future as not everyone is looking for ‘advanced’ tutorials.