Ugh lost a whole day of work because Excel + dates = UNENDING INSANITY.
So the library I use to import data from Excel to R has a handy convertToDate() function, so when importing I had it run on any columns with “date” in the name. Except I found it only works if
a) the column is formatted in Excel as a date, and
b) entries into that column are only typed directly into Excel, not copy-pasted strings or dragged over or inserted via external scripts etc etc etc.
So in a collaborative spreadsheet, some cells will be 16/6/2025 and others 2025-06-16 (for example). Even if you go into Excel and reapply the date formatting, change it to custom formatting, all of that, it still imports weird.
So when using the read_excel() function, some of those cells import as “52047” (Excel’s version of a date, eg days from a specific date) and others as the entered string “2025-06-16” or even “16/06/2025” if it’s super weird.
Lyndal
npub14g…dsgp5
2025-06-16 07:49:51 UTC
Author Public Key
npub14gsv9xcl2ta6p0gp4xy70yadnvw2gmj5lch8u4kle26hu7k2tydq7dsgp5Seen on
wss://relay.momostr.pinkPublished at
2025-06-16 07:49:51 UTCEvent JSON
{
"id": "d5ed7a7d4ca5ee70cb0ead22577e73c903ceb6dd61c5383986cb9524b2d877f8",
"pubkey": "aa20c29b1f52fba0bd01a989e793ad9b1ca46e54fe2e7e56dfcab57e7aca591a",
"created_at": 1750060191,
"kind": 1,
"tags": [
[
"content-warning",
"Excel + R work rant"
],
[
"proxy",
"https://bne.social/@lyndaljane/114691944708690523",
"web"
],
[
"proxy",
"https://bne.social/users/lyndaljane/statuses/114691944708690523",
"activitypub"
],
[
"L",
"pink.momostr"
],
[
"l",
"pink.momostr.activitypub:https://bne.social/users/lyndaljane/statuses/114691944708690523",
"pink.momostr"
],
[
"-"
]
],
"content": "Ugh lost a whole day of work because Excel + dates = UNENDING INSANITY. \n\nSo the library I use to import data from Excel to R has a handy convertToDate() function, so when importing I had it run on any columns with “date” in the name. Except I found it only works if \na) the column is formatted in Excel as a date, and \nb) entries into that column are only typed directly into Excel, not copy-pasted strings or dragged over or inserted via external scripts etc etc etc.\n\nSo in a collaborative spreadsheet, some cells will be 16/6/2025 and others 2025-06-16 (for example). Even if you go into Excel and reapply the date formatting, change it to custom formatting, all of that, it still imports weird. \n\nSo when using the read_excel() function, some of those cells import as “52047” (Excel’s version of a date, eg days from a specific date) and others as the entered string “2025-06-16” or even “16/06/2025” if it’s super weird.",
"sig": "70e34e8e86f04196874603c7354de9e0d7ba7d3bc14e9804541cc1fa0ddbd44ae8004b617c55e524bb5ae1e5f1d35bade654164a952b60577eca653834849424"
}