Cell History Changing Due to Time Zone for Column Formula?

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

Find more posts tagged with

Formulas

Accepted answers

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

All comments

Bassam Khalil

Hi @CJKONEGA

=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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.