ONLYOFFICE DocSpace released: improve document collaboration with offices, customers, and partners. Use it for free!
ONLYOFFICE DocSpace released

Formatting dates into SUBSTITUTE correctly

I’m trying to use the substitute function to format a date into a string.

On the left is the date, on top is the initial string, and on the bottom is the final string.


The final string formula: =SUBSTITUTE(B1,"{DATE}",A2)

I want the final string to look like due:2023-04-20+22h, but the SUBSTITUTE function is not outputting 2023-04-20, instead it’s outputting 45036. I suppose this is the number of days since epoch 1900-01-01.

How do I get the date to format properly within this substitute?

Figured it out. Can use TEXT(A2,"yyyy-mm-dd") format it correctly.

There’s no button to mark as solved so I’ll just leave this here I suppose

Hello @dextertd

Glad to hear that you found a solution.