Cell History Changing Due to Time Zone for Column Formula?

YAHOO MAN

Hi,

I have a column level formula and the oddest thing is happening. My cell value keeps changing to a swapping date when the actual cell containing the date has not changed (cell history checked). I'm hunching that a user is using different timezone date format which is why the dates flip between MM/DD and DD/MM and back and forth. Below is my formula.

Column Formula: ="Cancelled Sale: " + [Village ID]@row + CHAR(10) + "Villa: " + [Villa Code]@row + CHAR(10) + "Sale Type: " + [New or Resale]@row + CHAR(10) + "Surname: " + [Purchase SurnameName 1]@row + CHAR(10) + "Salesperson: " + Salesperson@row + CHAR(10) + "Date of Original Sale: " + [Original Date of Sale]@row

Bassam Khalil

Hi @CJKONEGA 

Hope you are fine, you can solve this problem by creating a date type helper column [Rectified Date] and use a fixed format for it for example ( yyyy-mm-dd ) and use the following formula as a column format formula in this column :

=IFERROR(DATE(YEAR([Original Date of Sale]@row), MONTH([Original Date of Sale]@row), 
DAY([Original Date of Sale]@row)), "")

the following screenshot shows the result:

then you can use this column [Rectified Date] in your original formula so your new formula will be

Column Formula: ="Cancelled Sale: " + [Village ID]@row + CHAR(10) + "Villa: " + [Villa Code]@row +
CHAR(10) + "Sale Type: " + [New or Resale]@row + CHAR(10) + "Surname: " +
[Purchase SurnameName 1]@row + CHAR(10) + "Salesperson: " + Salesperson@row + CHAR(10)
+ "Date of Original Sale: " + [Rectified Date]@row

Bassam Khalil

Hi @CJKONEGA 

Hope you are fine, you can solve this problem by creating a date type helper column [Rectified Date] and use a fixed format for it for example ( yyyy-mm-dd ) and use the following formula as a column format formula in this column :

=IFERROR(DATE(YEAR([Original Date of Sale]@row), MONTH([Original Date of Sale]@row), 
DAY([Original Date of Sale]@row)), "")

the following screenshot shows the result:

then you can use this column [Rectified Date] in your original formula so your new formula will be

Column Formula: ="Cancelled Sale: " + [Village ID]@row + CHAR(10) + "Villa: " + [Villa Code]@row +
CHAR(10) + "Sale Type: " + [New or Resale]@row + CHAR(10) + "Surname: " +
[Purchase SurnameName 1]@row + CHAR(10) + "Salesperson: " + Salesperson@row + CHAR(10)
+ "Date of Original Sale: " + [Rectified Date]@row

YAHOO MAN

Hey @Bassam Khalil thanks for the tip. That has worked...thank you so much for your help.

Bassam Khalil

@YAHOO MAN 

You are welcome and i will be happy to help you any time.