↑ Return to Date & Time

Generality & Format

In Excel, dates are false friends because there are a multitude of characteristics which are complicating their use in calculations.

Presentation

Rule 1: Dates are numbers.


To verify this, write any Date in a cell and change its format to the standard mode. There, you can see that the value of your date is turned in a figure.

This figure represents the number of days from 01/01/1900 (which is the starting point dates in Excel). It is also possible to start at 01/01/1904, but this will be studied for the formatting of hours at the bottom of this page.

On the opposite, if you have a date that is left justified, it is not a date within the meaning of Excel as shown in this example.

 

 

If you want to see exactly Saturday, January 1, 2011 in a cell, you write it as a date but in a particular format (see date’s format below). As this data is right justified in the cell, it is indeed a figure even if it looks like a text.

 

 

Rule 2: The value 1 is always one day.

This rule is very important to understand how to make calculations about a date and also how to work with hours.

For example, if you have the value 8 in a cell and you apply the format ‘Time’, your cell displays 00:00:00.

This is not an error. Excel displays the eighth day from 01/01/1900.

Rule 3: Times are a fraction of a day.

If you want to see 8:00:00 in a cell, divide that figure by the number of hours in a day (24). Then, you apply a time format and you see 8:00 and not 0.33333333.

 

 

 

 

Date format

When you have a date in a cell, you can display it in different ways due to formatting options. In the ribbon, you have the option short date and long date.

But you can customize each component of a date (day / month / year) using the customization dialog box numbers. To display the dialog box to customize numbers, you click the arrow just below the ribbon or by activating the keyboard shortcut Ctrl + 1. In this dialog box, you select in the right part the option ‘Custom’ and you fill your format in the ‘Type’ zone.

Each component of a date can be changed:

  • d for days
  • m for the months
  • has for years

In the ‘Type’,simply indicate how you want to display the date based on the example of the table.

But you can also combine all these types to display dates in different ways and in all cases these dates are numbers as they are right justified.

Format of Hours

In the same way as for dates, it is also possible to change the format of hours (h), minutes (m) and seconds (s). But there are special.

  • To view hours beyond 24, you must write code for hours in brackets [h]. In the example below, Excel shows the number of hours since the 01/01/1900
  • It is not possible to write the minutes without the time or without the second because otherwise the format is interpreted as 1 month
  • For tenths, cents and beyond, simply add 0 to format to display your information in your cell

The following table outlines differences with the corresponding code.

Leave a Reply

%d bloggers like this: