Skip across the chapter
Quota Playbook

Date checks for a ramp tracker sheet: 5 steps

Quota Playbook Editorial Team · Published · 11 min read

Check that DATE inputs are whole numbers, years fall in the valid range, and months and days do not roll over. DATE silently recalculates out-of-range months and days, according to Google Docs Editors Help, so errors can hide in a ramp sheet.

Key takeaways

  • test a month of 13 in a scratch cell before you trust the sheet
  • keep every date input as a number, not text
  • keep each year in the valid range before the date forms
  • enter whole-number months and days in every ramp row
  • compare each built date with a hand count before a team reads it

Run every test in a scratch copy of the sheet, not in the rows your team reads. This guide does not treat Google Sheets and EspoCRM as sharing the same date rules.

Why a ramp tracker needs date checks

A ramp tracker records the dates that mark a new seller's progress through onboarding, and many of its counts come from those dates. For the milestone list, see new-seller onboarding time milestones. If one date is built wrong, every count that uses it moves with it. This guide covers the DATE function in Google Sheets, so each check asks one question: does the date the formula builds match the date you meant?

A test row lets you see whether your sheet builds the date you meant before a real seller depends on it. Your test rows can each cover one kind of problem, and each row can carry a clear label. Keep the test rows separate from the rows your team reads, so a deliberate error never reaches a seller's schedule.

A date that looks reasonable does not prove the input was valid, because the sheet displays whatever date the formula built. Your review should compare each built date with the date you intended for that row. Compare the month, the day, and the year separately, since one wrong number changes the schedule for that seller.

The checks in this guide draw on the DATE page of Google Docs Editors Help and on the formula page of EspoCRM, and the two products are different. Your ramp sheet is the thing you verify, so keep every check tied to it.

Step 1: confirm inputs are numbers, not text

Inputs must be numbers, not text

Inputs to DATE must be numbers - if a string or a reference to a cell containing a string is provided, the #VALUE! error will be returned, according to Google Docs Editors Help, so a month typed as words stops the date from forming. Read the error as the first place to look when a row fails. That first look should cover every input cell in the row, not just the one you suspect.

You can type one seller's start month as a number in a test row, then as text in a second row, and compare what each row returns. The numeric row should produce a date, and the text row should show the error that the rule names. A result that differs from that pattern is a reason to inspect the cell again.

Label each input column with its own heading, and keep comments in a separate column so every value cell holds a value. During each review, scan one input column at a time from top to bottom, and stop on any cell whose contents look like a word instead of a number. Mark those cells in a note, so the same cell does not slip past the next review.

When a value arrives from a paste or an import, read it again before the date formula uses it, since the error covers text values. A note about the source gives the reviewer a place to start. Keep that note beside the value rather than in a separate file. If the source is a shared sheet, record which person or tool supplied the value.

Your check for this section is complete when every month input, day input, and year input cell holds a number in each row. A row that fails should be fixed at the input, not patched in the date formula, so the formula stays the same across rows.

Step 2: confirm the year is in range

Years must fall in the valid range

For years 1900 to 9999, Google Sheets will use that value as the year, according to Google Docs Editors Help, so the ramp year needs to sit inside that span. Read the year column as a whole and look for any entry outside those bounds, because one bad entry in a long sheet is easy to miss.

For years less than 0 or greater than 10,000, Google Sheets will return the #NUM! error value, according to Google Docs Editors Help, so a year outside those limits shows the error instead of a date. That error is a signal to fix the year input before you read any month or day value. Fix the year in the same row where the error appears.

You can type a year of 9999 into a test cell and compare the result with the year you planned for that row. Next, type a year above 10,000 in a second test cell, and confirm that it shows the #NUM! error the rule describes. If the second cell does not show that error, the year input in that row needs a second look before the sheet reads it.

Keep the year in its own column, so you can scan one value in each row without reading the month or day beside it.

When a year returns the error, fix the year input and rebuild the row rather than typing a date over the formula. A typed date can hide the problem from the next reviewer. Record the seller name and the corrected year in a note so the fix can be checked later. Keep that note with the sheet, not in a message thread.

Step 3: watch for months and days that roll over

Out-of-range months and days roll over

For example, DATE(1969,13,1), which specifies the illegal month 13, will create a date of 1/1/1970, according to Google Docs Editors Help, so a month of 13 moves into the next year. Read the built date next to the month you typed, and do not accept a date that lands in a different year without a second look. Mark that row so the review can revisit it.

Similarly, DATE(1969,1,32), which specifies the non-existent 32nd day of January, will create a date of 2/1/1969, according to Google Docs Editors Help, so a day of 32 moves into the following month. A day that is too large for its month belongs in your test row, because that is the case the quoted example covers. Compare the built date with the calendar for the month you meant, and mark any row where the month or the day changed.

Check each month and day cell in the ramp sheet against a calendar before a schedule goes to your team. Read every built date in a column at once, since one row can look normal while a neighbor does not. You can keep a short list of rows you reviewed, with the date you checked.

You can build a small test table with a month of 13, a day of 32, and a leap-day input, then compare each built date to the calendar you expect. You can record which rows matched and which did not, so the results can be repeated after the next edit. The table can sit next to the ramp sheet, with each row labeled by the rule it tests.

Your month and day columns should hold numbers, and each value should be checked against the valid months and days before the sheet builds a date from it. A value that passes this review still needs the hand count at the end of this guide. Keep these checks in place each time the sheet grows.

Illustrative example: a month of 13

Suppose a ramp row has a year of 2026, a month of 13 and a day of 1. Because a month of 13 moves into the next year, the built date should fall in the year after 2026, not in 2026. A row with a month of 1 and the same year would build a date in 2026, so the two results differ by a year. Type both test rows in a scratch copy, then record the built date beside the date you meant.

Step 4: watch for decimals that truncate

Decimals are silently truncated

DATE will silently truncate decimal values input into the function, for example a month of 12.75 will be interpreted as 12, according to Google Docs Editors Help, so a fractional month loses its decimal part. Check the month and day columns for fractions before you trust a date, since a fraction can come from a calculated value.

You can type 12.75 into a test cell for the month and read back the month the sheet builds, then compare that month with 12. Repeat the test with a decimal day, and compare the built day with the whole number part of your entry.

Enter whole-number months and days in every ramp row, so the built date does not depend on how a decimal would be read. A whole-number entry is also easier to compare with a hand count, because each value matches the calendar exactly.

You can label the decimal test row, so the result can be repeated after any edit to the formula.

Your check is finished when every month and day column holds whole numbers from the first row to the last. A decimal-free column also makes the hand count simpler to run, since each value is a whole day. If a fraction slips in later, repeat the test row before the schedule goes out.

Step 5: compare dates with a hand count

If you compare an int 1 to a float 1.0 with == operator, they are treated as not equal, according to EspoCRM, so an int and a float with the same value can fail an equality test. That page describes EspoCRM's formula script, not the Google Sheets DATE function.

The EspoCRM formula page says 'You need to compare values of same types', according to EspoCRM. You can write the date the same way in both places before you compare them. For a hand count, write each part of the date as a whole number.

Your hand count can start from the same reference point, as the DATE page says 'It counts the days since December 30, 1899 (not including December 30, 1899)', according to Google Docs Editors Help, so count days from that point. For one seller, count the days on paper starting after the reference point, and compare the total with the sheet.

After one seller passes, repeat the hand count for a few more rows, choosing rows with different start years and different month-end dates. Record each comparison in the sheet's notes so the next reviewer can see what was checked.

The table below sets each source's wording beside the section where this guide uses it.

Publisher Source page Source wording Use in this guide
Google Docs Editors Help DATE page DATE will silently recalculate numeric dates which fall outside of valid month or day ranges. Month and day checks
EspoCRM Formula script page If you compare an int 1 to a float 1.0 with == operator, they are treated as not equal. Hand-count comparison, EspoCRM only

Your next step: test the ramp sheet with a month of 13 in a scratch cell, then compare the built date with the month you intended. DATE moves month 13 into the next year without showing an error, for example DATE(1969,13,1) gives 1/1/1970. Fix the cell only if the built date differs from the intended one. Save the test row with the ramp sheet's notes so the next edit is checked the same way.

FAQ

What does DATE do with month 13?

DATE moves a month of 13 into the next year, so the sheet can build a date you did not type, according to Google Docs Editors Help. Test that input in a scratch cell, and compare the built date with the month you meant before you trust the row.

Why does my date show #VALUE!?

Text in a month, day, or year input returns the #VALUE! error, and so does a reference to a cell that holds text, according to Google Docs Editors Help. Convert the input to a number, check the source cell too, then recheck the row.

Does DATE round decimal months?

No, DATE truncates decimal values instead of rounding them, according to Google Docs Editors Help, so a month of 12.75 is read as 12. Enter whole-number months in the ramp rows, and use a decimal test row to confirm the built month.

What years return #NUM!?

Years below 0 and above 10,000 return the #NUM! error, according to Google Docs Editors Help, so check the year cell first. Keep ramp years inside 1900 to 9999, and test a year above 10,000 in a scratch cell to confirm the error appears.

Does the EspoCRM comparison rule apply to Google Sheets?

No. The EspoCRM formula page describes its own comparison behavior, according to EspoCRM, so that page does not set a Sheets rule. You can use its example as a reminder to match types in your hand count.

Where should the date checks start?

The order in this guide runs from inputs to year to month and day, then decimals, then a hand count. Each stage covers one rule from the sections above. Keep the test row in the sheet, so you can run these checks again after each edit.

Sources

  1. New-seller onboarding time: track it with 3 milestone parts
  2. Backup admin checklist: 8 checks for sales tool logins
  3. Check the 28-day backup line before a practice reset
  4. Departing rep's Mac
  5. Tiered commission plan

More plays