Don't understand date formatting, why it shows wrong date?

Ask us anything
1

image
image734×574 44.8 KB

I have a formula like such that gives me a date.

But when I try to format it to YYYY-MM-DD, it gives me 2024-07-21 and not 2024-07-20. Why? How do I get it to show the correct date?

image
image724×414 33.6 KB

2

what timezone are you in?

3

GMT+8. Does it matter?

4

Yes. “Z” means the time zone is Greenwich Mean Time (so-called “Zulu time”). So 1600 hours (4pm) in Greenwich, England is 0000 hours (midnight, because 16+8 = 24) in your local time.

formatDate uses your local time zone to convert a universal timestamp to your local representation.

If the 20 is correct, you might instead of using a date function, use subtext with a start of 0 and length of 10. (The first 10 characters of your existing ISO representation being 2024-07-20)