Help to set a time column to CST time zone
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!
Best Answer

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.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers

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.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

@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!!

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?

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
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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)

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.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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:

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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!