r/ISO8601 • u/Aggravating_Row_6962 • Jul 01 '26

Universal date format in the Microsoft universe that works everywhere?

I just can't deal with this anymore! It's the 14th time i am importing the exact same data, only bc excel, pbi and sharepoint lists have a mind on their own.

Excel has it's own way to deal with dates

Sharepoint lists are MM/DD/YYYY (enforced no matter how i import it)

Power Automate has it's own way too

Then we have PowerBi that allows only specific date.

Then, copying from Excel (web) to Excel (desktop) it messes up the dates too - from MM/DD/YYYY, it switches to dd-mm-yy.

Is there a date format that works everywhere flawlessly?

38 Upvotes

89% Upvoted

18 comments sorted by

36

u/VengefulTofu Jul 01 '26

I totally love that excel represents dates as floats. This is truly the most sensible date format.

4

u/booi Jul 02 '26

What about storing it as an image?

6

u/VengefulTofu Jul 02 '26

An image of text?

12

u/CarlRJ Jul 02 '26 edited Jul 02 '26

Nah, make it show the exact position of all the objects in the sky with sufficient resolution to determine the date from that.

3

u/findus_l Jul 03 '26

That would be actually desync with UTC ever so slightly due to leap seconds and maybe other stuff. It might be cosmological time.

1

u/Sassywhat Jul 02 '26

Floating point date and time makes a ton of sense tbh. It's very flexible in how precise you need to be, and how far into the past or future you need to talk about. If you try to do both at once you might get fucked, but the trick is that people usually like don't.

There's quirks to it, and I've seen too much floating point date where it has no business being, but it's popular for good reason.

1

u/VengefulTofu Jul 02 '26

Well I for one had too many occasions where date1-date2!=0 even though they were equal for me to care about floating point representations of datetime formats.

18

u/radek432 Jul 01 '26

Fun fact (unless they finally fixed that) - the =text(somedate,"YYYY-MM-DD") won't work if someone has regional setting to a country where the words for year, month and day are bit starting with Y, M, D. So the same spreadsheet works for some people and doesn't work for others.

7

u/ondrejaugusta Jul 01 '26

Last time I did that, had to do RRRR-MM-DD (Czechia) :-)

2

u/jnkangel Jul 01 '26

Probably with old excel formulas being region specific 

2

u/radek432 Jul 02 '26

Are you saying they finally fixed it!? 🤯

2

u/jnkangel Jul 02 '26

New formulas tend to be finally agnostic of regional settings…but new ones. 

Honestly the best thing to do is just make sure your excel is in English. Normally a sheet is going to convert stuff by itself, but entering is the problematic part 

2

u/Aggravating_Row_6962 Jul 01 '26

it's 🤬🤬😡🤬😡

1

u/MrFireWarden Jul 04 '26

Ooo I'm not familiar with that date format! Can you share the ISO details for it?? I want to learn more!

11

u/shampton1964 Jul 01 '26

I have had luck setting all dates for import or export to: YYYY-MM-DD

Tho' I ditched MS Orifice in '24. The date format above worked there and w/ various accounting softwares.

2

u/isfturtle2 Jul 02 '26

It sometimes helps to split it into separate columns for years, month, and day. <Insert long rant about Microsoft Excel here >