Chapter 12: Using Formulas with Dates and Times . . . . . . . . . . . . . . . . . . . . . . . . . . . .
12
Depending on your regional settings, entering a date in a format such as June 18, 2018, may be interpreted as a text string.
In such a case, you need to enter the date in a format that corresponds to your regional settings, such as 18 June 2018.
When you activate a cell that contains a date, the Formula bar shows the cell contents for-
matted by using the default date format—which corresponds to your system’s short date
format. The Formula bar doesn’t display the date’s serial number. If you need to find out the
serial number for a particular date, format the cell with the General format.To change the default date format, you need to change a systemwide setting. From the Windows Control Panel, select
Clock and Region and then click Region to open the Region dialog box. The exact procedure varies, depending on the
version of Windows you use. Look for the drop-down list that lets you change the Short Date format. The setting you
choose determines the default date format that Excel uses to display dates in the Formula bar.
Excel is rather flexible when it comes to recognizing dates entered into a cell. It’s not per-
fect, however. If you attempt to enter a date that lies outside the supported date range,
Excel interprets it as text. If you attempt to format a serial number that lies outside the
supported range as a date, the value displays as a series of hash marks (#########).Searching for Dates
If your worksheet uses many dates, you may need to search for a particular date by using the Find and
Replace dialog box (Home ➪ Editing ➪ Find & Select ➪ Find, or Ctrl+F). Excel is rather picky when
it comes to finding dates. You must enter the date as it appears in the Formula bar. For example, if a
cell contains a date formatted to display as June 19, 2016, the date appears in the Formula bar using
your system’s short date format (for example, 6/19/2016). Therefore, if you search for the date as
it appears in the cell, Excel won’t find it. But it will find the cell if you search for the date in the format
that appears in the Formula bar.Understanding time serial numbers
When you need to work with time values, you extend the Excel date serial number system
to include decimals. In other words, Excel works with time by using fractional days. For
example, the date serial number for June 1, 2016, is 42522. Noon (halfway through the day)
is represented internally as 42522.5.The serial number equivalent of one minute is approximately 0.00069444. The following for-
mula calculates this number by multiplying 24 hours by 60 minutes and dividing the result
into 1. The denominator consists of the number of minutes in a day (1,440).=1/(24*60)