Help to set a time column to CST time zone

Xochitl C.

We have a daily task at the beginning of the day in which the first thing to do is to review and track the number of tickets accumulated outside of working hours from our system. The timestamp column comes from the system with the format "datetime", in another separate Smartsheet we are trying to show the date on the top of a table, then the timestamp with the counts of tickets generated outside working hours, that is why I have separated it into 2 columns using a formula to extract the date and time (although it would be preferable to keep the date and time if possible). 

The problem I now face is that the system generates this time from somewhere in Asia and I need to convert it to CST along with the date, just in the event that the date recorded is changing due the CST time zone.

I have used the TIME formula to subtract 6 hours from the TIME column, and although I thought it might work, I see that at 00 hours it does not give me the expected result.

Showing a couple of screenshots as context of how I extracted the date and time into separate columns, just in case this is causing the error.

Extracting Date:

Extracting time:

My try to convert into CST:

The results (highlighting the no sense results):

Any help, I greatly appreciated!

Bassam Khalil

Hi @Xochitl C,

pleas try the following

1- create a helper column call it Time to collect the time from Timestamp column and use the following formula to do that and convert it to column format formula:

=MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, LEN(Timestamp@row))

2- in HOUR column use the following formula to collect the HOUR from the time column and convert it to column format formula

=VALUE(LEFT(Time@row, FIND(":", Time@row) - 1))

3- in DATE column use the following formula to collect the DAE from Timestamp column and convert it to column format formula:

=LEFT(Timestamp@row, FIND(" ", Timestamp@row) - 1)

4- create helper column call it Minutes and use the following formula to collect the Minutes from the time column and convert it to column format formula

=RIGHT(Time@row, 2)

5- finally use the following formula to convert your Timestamp to CST version:

=DATE@row + "  " + IF(HOUR@row < 6, HOUR@row + 24 - 6, HOUR@row - 6) + ":" + Minutes@row

the following screenshot shows the result

Bassam Khalil

Hi @Xochitl C,

pleas try the following

1- create a helper column call it Time to collect the time from Timestamp column and use the following formula to do that and convert it to column format formula:

=MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, LEN(Timestamp@row))

2- in HOUR column use the following formula to collect the HOUR from the time column and convert it to column format formula

=VALUE(LEFT(Time@row, FIND(":", Time@row) - 1))

3- in DATE column use the following formula to collect the DAE from Timestamp column and convert it to column format formula:

=LEFT(Timestamp@row, FIND(" ", Timestamp@row) - 1)

4- create helper column call it Minutes and use the following formula to collect the Minutes from the time column and convert it to column format formula

=RIGHT(Time@row, 2)

5- finally use the following formula to convert your Timestamp to CST version:

=DATE@row + "  " + IF(HOUR@row < 6, HOUR@row + 24 - 6, HOUR@row - 6) + ":" + Minutes@row

the following screenshot shows the result

Xochitl C.

https://community.smartsheet.com/discussion/comment/423015#Comment_423015

@Bassam Khalil this is more than perfect because I wanted to keep date and time in a column. Thanks so much!!!!!!!!! very detailed explanation I appreciate it!!

Xochitl C.

https://community.smartsheet.com/discussion/comment/423015#Comment_423015

Hey @Bassam Khalil any idea how can I adjust the formula in the column CST TIME, so when it detects the date changes?

For example the original date in timestamp is march 2nd 1:16 am, so at CST it should be march 1st?

Bassam Khalil

Hi @Xochitl C,

Please replace the formula in CST TIME column with the following formula and i hope it will solve your problem to adjust the date:

=IF(HOUR@row > 6, LEFT(DATE@row, FIND("/", DATE@row) - 1), VALUE(LEFT(DATE@row, FIND("/",
 DATE@row) - 1)) - 1) + "/" + MID(DATE@row, FIND("/", DATE@row) + 1, FIND("/", DATE@row,
 FIND("/", DATE@row) + 1) - FIND("/", DATE@row) - 1) + "/" + RIGHT(DATE@row, 4) + "  " + IF(HOUR@row < 6,
 HOUR@row + 24 - 6, HOUR@row - 6) + ":" + Minutes@row

the following screenshot shows the result

Xochitl C.

https://community.smartsheet.com/discussion/comment/423047#Comment_423047

Hi @Bassam Khalil , thanks, but this is not working. As you see the timestamp is set as m/dd/yyyy hh:mm, so the formula result is changing the month instead the date after 12 hours.

Highlighting the cells with yellow when the CST time is at (or after 6 pm)

Bassam Khalil

Hi @Xochitl C.,

sorry i created this formula on my local setting dd/mm/yyyy, and it's work perfectly i will check it for you to convert the format.

Bassam Khalil

Hi @Xochitl C.,

please try the following formula and it will work with your DATE format m/dd/yyyy:

=VALUE(LEFT(DATE@row, FIND("/", DATE@row) - 1)) + "/" + IF(HOUR@row < 6, VALUE(MID(DATE@row,
 FIND("/", DATE@row) + 1, FIND("/", DATE@row, FIND("/", DATE@row) + 1) - FIND("/", DATE@row) - 1)) - 1,
 (VALUE(MID(DATE@row, FIND("/", DATE@row) + 1, FIND("/", DATE@row, FIND("/",
 DATE@row) + 1) - FIND("/", DATE@row) - 1)))) + "/" + RIGHT(DATE@row, 4) + " " + "  " + IF(HOUR@row < 6,
 HOUR@row + 24 - 6 + ":" + Minutes@row, HOUR@row - 6 + ":" + Minutes@row)

the following screenshot shows the result:

Xochitl C.

https://community.smartsheet.com/discussion/comment/423243#Comment_423243

Hi @Bassam Khalil, I appreciate you taking the time to help me to figure out, but I think we still having a problem when the difference between time zone changes to another month. For instance the recurrences starting on day 1, the result should be the last day of the previous month and after testing your formula in these cases I have a day zero which is not the expected result :(

See what I mean:

Bassam Khalil

Hi @Xochitl C.,

Could you please grant me admin access to a copy of your sheet, after removing or hiding any sensitive data? This way, I can create your formula using the same Timestamp and address all issues.

nichadam

@Bassam Khalil I'm trying to do this exact same thing, and followed your advice to the end. Was there a solution to the answer for Xochitl's last question?