Dates that know they're dates: working with date columns

Dates in TukeySheets are real values, not text that happens to look like a date. A cell that holds March 15, 2024 knows it is a day on the calendar, so you can subtract it from another day, shift it forward two weeks, or pull the month out of it, and you always get the right answer. This post shows you how to enter dates, compute with them, and reach for the right helper function when you need one.

You will learn four things, in order:

1. How to set up a date column and enter values into it.
2. How to do arithmetic directly with + and -.
3. How to use the helper functions that build, shift, and measure dates.
4. How to combine these pieces in a short, practical example.

Start at the top and stop when you have what you need.

Set up a date column first

Give the column a date type, then type dates the natural way. When a column is typed as a date, every value you enter is parsed and stored as a calendar day. The display format is yours to choose, but the stored value is always the same underlying day, which is what makes the math reliable.

Three input forms are recognized:

• ISO form, written 2024-03-15. This form is never ambiguous, so prefer it.
• Short local form, written 3/15/2024.
• Two-digit years, written 3/15/24. A two-digit year from 00 to 49 becomes 2000 through 2049, and a two-digit year from 50 to 99 becomes 1950 through 1999.

TukeySheets has four time-related types, and each one answers a different question:

• Date holds a calendar day, such as 2024-03-15.
• Time holds a clock reading within a day, such as 14:30:00.
• Timestamp holds a specific instant, a day plus a time, such as 2024-03-15 14:30:00.
• Duration holds a length of time, such as three days or ninety minutes.

The difference matters because the type travels with the value. Subtract one date from another and you get a Duration, not a meaningless number, and the result carries that meaning into the next calculation.

Do arithmetic with plain + and -

Once a column holds real dates, the ordinary math operators work the way you would hope. You do not need a function for the common cases.

• Add days to a date. If A1 holds 2024-03-15, then =A1 + 7 returns 2024-03-22, one week later.
• Subtract days from a date. =A1 - 30 returns the day thirty days earlier.
• Find the span between two dates. =A2 - A1 returns a Duration, the time from the first day to the second.
• Shift a timestamp. Adding to a Timestamp moves the instant forward, and subtracting two timestamps gives you the Duration between them.

Use the operators for quick, day-level work. When you need months, named units, or whole-number components, use the helper functions below.

Use the helper functions

The helper functions fall into four groups. Pick the group that matches your task, then pick the function inside it.

Build and read the clock

These create dates or read the current moment.

• DATE(year, month, day) builds a date from its parts. =DATE(2024, 3, 15) returns 2024-03-15. Month and day values that overflow roll forward, so =DATE(2024, 13, 1) returns 2025-01-01.
• DATEVALUE(date_text) turns a date written as text into a real date value.
• TODAY() returns the current date.
• NOW() returns the current date and time as a timestamp.

Pull a date apart

These return a single component as a number.

• YEAR(date), MONTH(date), and DAY(date) return the year, the month from 1 to 12, and the day from 1 to 31.
• HOUR(datetime), MINUTE(datetime), and SECOND(datetime) return the clock components.
• WEEKDAY(date, [type]) returns the day of the week as a number. With the default type, Sunday is 1 and Saturday is 7. Pass type 2 to make Monday 1 and Sunday 7.
• WEEKNUM(date) returns the week number within the year.

Shift a date forward or back

These move a date by a chosen amount and return a new date.

• DATEADD(date, amount, unit) is the general tool. The unit is one of second, minute, hour, day, week, month, quarter, or year, and common short forms such as d, wk, mo, and yr also work. The amount may be negative.
  • =DATEADD(A1, 2, "week") returns the day two weeks later.
  • =DATEADD(A1, -1, "year") returns the same calendar day one year earlier.
  • Month, quarter, and year are calendar units, so they clamp the day to the end of the target month. Adding one month to January 31 returns February 29 in a leap year and February 28 otherwise.
  • Adding a sub-day unit to a date, such as =DATEADD(A1, 1, "hour"), produces a timestamp, because the result now carries a time of day and TukeySheets will not quietly drop it.
• EDATE(start_date, months) adds a whole number of months and returns a date. It is the focused tool when you only ever shift by months.
• EOMONTH(start_date, months) returns the last day of the month that is the given number of months away. =EOMONTH(A1, 0) returns the last day of the current month.

Measure the distance between dates

These return how far apart two dates are.

• DAYS(end_date, start_date) returns the number of days between two dates as a plain number.
• DATEDIFF(start_date, end_date, unit) returns the difference in a chosen unit. Use "Y" for full years, "M" for full months, and "D" for days. The start date comes first.

Put it together

Suppose column A holds project start dates and you want, for each project, a due date thirty days out, the end of that month for billing, and the number of days remaining from today.

1. Due date. In column B, write =DATEADD(A2, 30, "day"). Each row now shows the day thirty days after its start.
2. Billing close. In column C, write =EOMONTH(B2, 0). Each row shows the last day of the month the due date lands in.
3. Days remaining. In column D, write =DAYS(B2, TODAY()). Each row shows how many days are left until the due date, and the number updates as the calendar advances.

Three columns, three functions, and the dates stay correct through leap years and month boundaries because every value is a real day, not a string.

Summary

Type your columns as dates, and most of the work becomes simple. Use + and - for day-level arithmetic and date spans. Reach for DATEADD, EDATE, and EOMONTH to shift by named units, for DAYS and DATEDIFF to measure, and for the component functions to read a date apart. Because the value always knows it is a date, the answers stay right.