Dates and times

By the end of this lesson you can read dates written in different country styles and avoid the 03/04 trap.

Why it matters

A clinic in Lagos books a patient for 03/04. A computer set up for the United States reads it as March 4. The patient arrives on the 3rd of April and is not on the list. Dates cause real mix-ups every day, and they are easy to avoid once you know the trap.

The idea in plain words

A date has three parts: the day, the month and the year. The trouble is that countries write them in different orders.

• In Nigeria, the United Kingdom and Colombia, people usually write the day first: 03/04/2026 means 3 April 2026.
• In the United States, people usually write the month first: 03/04/2026 means March 4, 2026.

Same eight digits, two different days, a month apart.

Only the year-first style means the same thing everywhere.

There is a style that never confuses: year, then month, then day, with dashes. 2026-04-03 is always 3 April. It is an international standard, called ISO 8601, and all the datasets in this course use it.

ISO date: a date written year-month-day, such as 2026-03-02 for 2 March 2026. Nobody in any country reads it differently.

One more idea. A spreadsheet does not really store "2 March". It stores a day number. Google Sheets counts the days since 30 December 1899 (Google Docs Editors Help, DATE). That is why you can subtract one date from another and get a number of days.

See it done

Data: Amara's tab, market-stall-week.csv (synthetic). Dates are in column B, times in column D.

Question: how many days apart are Amara's first and last sales?

1. Read the two dates. B2 is 2026-03-02, the first sale. B61 is 2026-03-07, the last sale.
2. See the day numbers behind them. Click B2, then Format, Number, Number. The cell now shows 46083. Do the same for B61: 46088. Then press Ctrl + Z (⌘ + Z on a Mac) twice to put the dates back.
3. Subtract. Click an empty cell, such as I2, and type =B61-B2, then press Enter.

Formula: anything you type that starts with = is a formula: an instruction that tells the spreadsheet to work something out. You will learn many more in Module 3.

4. Read the answer. 5. That is 46088 − 46083 = 5. The first and last sales are 5 days apart (2 March to 7 March).

Behind each date is a day number, so taking one from the other gives days.

Tip: if the answer shows as a date (such as 1900-01-04) instead of 5, Sheets has guessed the wrong look for it. Click the cell, then Format, Number, Number. The value underneath is still 5.

Times work the same way. A time is part of a day. Sale S001 was at 07:14 and sale S060 at 18:41. =D61-D2 gives the gap between those two times of day: 11:27, which is 11 hours and 27 minutes. (Sheets may show it with seconds, as 11:27:00.)

Your turn, with help

Write each date in the ISO style (year-month-day).

1. 3 April 2026, written in Lagos as 03/04/2026.
2. July 9, 2026, written in New York as 07/09/2026.

Hint: first say which day and month it is in words. Then write year, month, day, with two digits for month and day.

Show the answer

1. 2026-04-03.
2. 2026-07-09.

Your turn, alone

1. A letter from Bogotá is dated 11/02/2026. Write it in ISO style.
2. What date is 2026-12-01, in words?
3. Lucía's survey was on 2026-03-16 and Amara's last sale on 2026-03-07. How many days apart are they? (Count on a calendar, or subtract in Sheets.)
4. Why is 31/12/2026 safe to read in any country, but 05/06/2026 is not?

Check your answers

1. 2026-02-11 (11 February, because Colombia writes the day first).
2. 1 December 2026.
3. 9 days.
4. There is no month 31, so 31 must be the day. In 05/06 both numbers could be a month, so it could be 5 June or May 6.

Watch out

• The 03/04 trap. When both numbers are 12 or less (and not the same), a slash date can be read two ways. Ask, or check another date in the same file (one with a number over 12 shows the order).
• Your spreadsheet's country setting. Google Sheets reads and shows dates using the spreadsheet's locale (its country setting). You can see it in File, Settings, Locale (Google Docs Editors Help). The same file can look different on a computer set up for another country.
• Dates saved as words. If a date sits on the left of its cell and subtracting gives an error, Sheets may be holding it as text, not as a day number.

Say it back

1. Write 25 December 2026 in ISO style.
2. What does a spreadsheet really store for a date?
3. What does 03/04/2026 mean in the United States?

Show the answers

1. 2026-12-25.
2. A day number (the count of days since 30 December 1899, in Google Sheets).
3. March 4, 2026.

What is next

Look back at the picture of Lucía's sheet in lesson 2.1. Row 7 had an empty box. One pupil just did not answer. Is that the same as writing 0? Next lesson.