Working with dates and times in Grid by Zaweba

Dates look simple until you try to count with them. How many working days until the deadline? When does a payment fall due if it's three months after an invoice? What age will a pupil be on the first of September? Grid by Zaweba has 25 date and time functions that turn these into one-line formulas.

How Grid stores a date

Behind every date in a spreadsheet is a plain number: the count of days since the start of 1900, with the time of day as the fraction after the decimal point. Grid uses the 1900 date system, including its long-standing quirk of treating 1900 as a leap year, so the serial numbers it stores match those in other spreadsheets. A workbook passed back and forth keeps the same dates, and formulas that subtract one date from another give the same answers.

Knowing this explains a lot. Subtracting two dates gives a number of days. Adding 7 to a date moves it on a week. And if a column of dates suddenly shows numbers like 45000, nothing is broken: the cells just need a date number format again.

Working days and deadlines

NETWORKDAYS.INTL counts the working days between two dates, and WORKDAY.INTL gives the date a set number of working days ahead. The .INTL versions let you say which days count as the weekend, which suits shift patterns and businesses that open on Saturdays. Both can take a list of holidays: keep the bank holidays in a small range on their own sheet, give it a name, and point the functions at that name.

These are the functions behind project timelines, delivery promises and holiday allowances. A column of start dates, a column of durations and a WORKDAY.INTL formula gives every due date at once.

Months, ages and fractions of a year

EDATE moves a date forward or back by whole months, which is the right way to add "three months" because months differ in length. EOMONTH gives the last day of a month, handy for billing periods and month-end reports. DATEDIF works out the difference between two dates in years, months or days, and is the usual way to calculate an age or a length of service. YEARFRAC gives the fraction of a year between two dates, which interest and pro rata calculations need.

ISOWEEKNUM returns the week number under the ISO rules that many European businesses and schools plan by, where week one is the week containing the first Thursday of the year.

Making dates behave

The commonest problem is text that only looks like a date. Data pasted from a website or exported from another system can arrive as text, and date functions can't count with it. If a column won't sort in date order, that's often why. Opening a CSV file in Grid detects the encoding and delimiter for you, but always check a few dates after importing.

Show dates in the format your readers expect. Format Cells has date and time categories, and custom codes can show long dates in the style of your locale or elapsed hours beyond 24 for timesheets. The value underneath stays the same whatever format you choose.

The fill handle is a good companion here. Type a date, drag down, and Grid fills a series of days; Fill Series adds date series too. Combined with WORKDAY.INTL, you can lay out a whole term's calendar in a minute.

Times of day

Because a time is a fraction of a day, a shift from 09:00 to 17:30 is simply the later time minus the earlier. Multiply by 24 to turn the result into hours for a pay calculation. For shifts past midnight, add one to the end time when it's smaller than the start.

Dates are some of the most shared data in any workbook. Since Grid by Zaweba follows the same date system as other spreadsheets, a timesheet or rota built here keeps its dates when it's saved as .xlsx and opened elsewhere.