Spreadsheet date logic

Continuing the discussion from BBEdit 15 Adds ChatGPT, Minimap, Cheat Sheets, and More:

This reminds me of a persistent annoyance with spreadsheets (definitely Excel, probably all the rest).

If you type in a date without a year (e.g. 1/15 or 12/5), it automatically applies the current year (creating 1/15/2024 and 12/5/2024, respectively, if tried today).

This is understandable and predictable, but it can be frustrating. For example, in December, I often need to record dates for the next month. But if I omit the year in my typing, I end up with January from the current year, not the next year. And vice versa - sometimes when in January I need to enter a December date from the previous year.

Years ago, a project I worked on for my employer had a date-parse function that did it a better way. When a date without a year was provided, it would auto-fill the year closest to the current day. So if I tried to expand 12/5 in January 2024, it would expand to 12/5/2023, since that date is closer to today than 12/5/2024. But if I tried to do that same expansion in July, it would pick 2024, since that would then be closer.

I’d love spreadsheets everywhere to adopt this convention.

2 Likes

I’d be ok if it was just for January and February.

FWIW, the finance app Moneydance does the same thing (e.g., adds the current year if you type just month/day), and I always do it at least once.

I usually use the calendar pop-up in Moneydance for selecting dates. A trick for entering a date within a few months of the current date is to type ‘t’ (for today) in the date field and then tap it to get the calendar pop-up and select the real date that you want.

You and me both. Just an hour ago, I noticed this bug had bitten me late last year for a date next month.

I suppose I should be grateful that I can choose my preferred date format.