Help to set a time column to CST time zone

Xochitl C.
Xochitl C. ✭✭✭✭
in Formulas and Functions

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:

image.png

Extracting time:

image.png

My try to convert into CST:

image.png

The results (highlighting the no sense results):

image.png

Any help, I greatly appreciated!

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    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

    image.png


    PMP Certified

    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"

    · Share on FacebookShare on Twitter

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    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

    image.png


    PMP Certified

    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"

    · Share on FacebookShare on Twitter
  • Xochitl C.
    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!!

    · Share on FacebookShare on Twitter
  • Xochitl C.
    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?

    image.png


    · Share on FacebookShare on Twitter
  • Bassam Khalil
    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

    image.png


    PMP Certified

    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"

    · Share on FacebookShare on Twitter
  • Xochitl C.
    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)

    image.png


    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/05/24

    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.

    PMP Certified

    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"

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    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:

    image.png


    PMP Certified

    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"

    · Share on FacebookShare on Twitter
  • Xochitl C.
    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:

    image.png


    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/07/24

    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.

    PMP Certified

    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"

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!