Spreadsheet date logic

TidBITS Talk
1

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
2

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.

3

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.

4

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.