Retain Date format when pulling data from another cell

Pamelam

I have one column [End Week Date] that uses a formula to provide the Monday Date of the week a task is supposed to end. This formula works.

I updated the [End Week Date] date format to YYYY-MM-DD.

My issue: I have another column [End Date Week Text] that I use the formula:

="Ending Week Of " + [End Week Date]@row

Expected Result if [End Week Date] = 2022-09-06 should be "Ending Week of 2022-09-06"

Actual Result: "Ending Week of 09/06/2022". This indicates to me that the re-formatting of the [End Week Date] column does not carry through in the text formula.

Any information on how to carry the formatting through would be greatly appreciated.

Thanks.

KT_H

Unfortunately to match your preferred formatting you would need to create some helper columns to support that formatting as it will always defer to the other formatting. To do this you would need three helper columns:

1. Month - End Week Date: =MONTH([End Week Date]@row)
2. Day - End Week Date: =DAY([End Week Date]@row)
3. Year - End Week Date: =YEAR([End Week Date]@row)

Then the formula in the [End Date Week Text] column would be this:

="Ending Week Of "+[Year - End Week Date]@row+"-"+[Month - End Week Date]@row+"-"+[Day - End Week Date]@row

It's a workaround but will do the trick.

KT_H

Unfortunately to match your preferred formatting you would need to create some helper columns to support that formatting as it will always defer to the other formatting. To do this you would need three helper columns:

1. Month - End Week Date: =MONTH([End Week Date]@row)
2. Day - End Week Date: =DAY([End Week Date]@row)
3. Year - End Week Date: =YEAR([End Week Date]@row)

Then the formula in the [End Date Week Text] column would be this:

="Ending Week Of "+[Year - End Week Date]@row+"-"+[Month - End Week Date]@row+"-"+[Day - End Week Date]@row

It's a workaround but will do the trick.

EvermoreCoffee

You may be able to tweak the formula of your [End Week Date Text] column a bit by using SUBSTITUTE() to swap out the '/' character with the '-' character (of course, this relies on the format remaining as mm/dd/yy:

="Ending Week Of " + SUBSTITUTE([End Week Date]@row,"/","-")

Pamelam

Thank you, this is very good to know in the future, but my issue is more that I wanted the year at the beginning. I did not know about the SUBSTITUTE function before. Now, I want to find a different reason for it.