Formula for days of week a ticket was submitted

Options

Hello,

I am looking for a formula to put the days of the week a form was submitted. i.e. monday, Tuesday, Wednesday, etc. I already have a created date column which i need to stay as it is but want another column for the specific day that it was.

Best Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓
    Options

    @Jeremy Oesch The formula below will do what you want. You just need to update to reference "Date@row" to match your column name.

    =IF(WEEKDAY(Date@row) = 1, "Sunday", IF(WEEKDAY(Date@row) = 2, "Monday", IF(WEEKDAY(Date@row) = 3, "Tuesday", IF(WEEKDAY(Date@row) = 4, "Wednesday", IF(WEEKDAY(Date@row) = 5, "Thursday", IF(WEEKDAY(Date@row) = 6, "Friday", IF(WEEKDAY(Date@row) = 7, "Saturday", "")))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    It is a time zone issue. The first step is to figure out what hour the day starts going forward by one. Once you know that, we can use a helper column (easier to manage) to output the correct date and then reference that helper column in your nested IF.

    In the below, replace the bold ## with whatever hour the date push happens.

    =DATEONLY([Date Opened]@row) - IF(AND(FIND("P", [Date Opened]@row) > 0, VALUE(MID([Date Opened]@row, FIND(" ", [Date Opened]@row) + 1, FIND(":", [Date Opened]@row) - (FIND(" ", [Date Opened]@row) + 1))) >= ##, VALUE(MID([Date Opened]@row, FIND(" ", [Date Opened]@row) + 1, FIND(":", [Date Opened]@row) - (FIND(" ", [Date Opened]@row) + 1))) < 12), 1, 0)

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓
    Options

    @Jeremy Oesch The formula below will do what you want. You just need to update to reference "Date@row" to match your column name.

    =IF(WEEKDAY(Date@row) = 1, "Sunday", IF(WEEKDAY(Date@row) = 2, "Monday", IF(WEEKDAY(Date@row) = 3, "Tuesday", IF(WEEKDAY(Date@row) = 4, "Wednesday", IF(WEEKDAY(Date@row) = 5, "Thursday", IF(WEEKDAY(Date@row) = 6, "Friday", IF(WEEKDAY(Date@row) = 7, "Saturday", "")))))))

  • Jeremy Oesch
    Options

    @JamesB

    It works great thank you very much!

  • Jeremy Oesch
    Options

    @JamesB @Paul Newcome,

    Okay sorry but i was looking more into it and it did not work. It does give me the days but the days are incorrect. For example, The first two rows are dates 05/10/2024. The first one gives me friday which is correct but the second one gives me saturday? Now all days apart from the first column are a day or more off.

  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

    @Jeremy Oesch Did you pull the formula down through the rows or did you make it a column formula.

    Check your formula in the working row and compare it to a non-working row. They should be exactly the same.

  • Jeremy Oesch
    Options

    @JamesB

    I converted it to a column formula for the "day of the week" column. Even when I create new column and pull them down they still have the same issue of being the wrong day. The other column "Date Opened" Isn't a formula and is just when a form was submitted and uses the column type date created. I don't have a "Working row" as it didnt work from the beginning. Sorry if I am misunderstanding what you are asking but I cant pull them down as we have a Dashboard that pulls data so i need it to be a column formula so it stays current.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    It is a time zone issue. The first step is to figure out what hour the day starts going forward by one. Once you know that, we can use a helper column (easier to manage) to output the correct date and then reference that helper column in your nested IF.

    In the below, replace the bold ## with whatever hour the date push happens.

    =DATEONLY([Date Opened]@row) - IF(AND(FIND("P", [Date Opened]@row) > 0, VALUE(MID([Date Opened]@row, FIND(" ", [Date Opened]@row) + 1, FIND(":", [Date Opened]@row) - (FIND(" ", [Date Opened]@row) + 1))) >= ##, VALUE(MID([Date Opened]@row, FIND(" ", [Date Opened]@row) + 1, FIND(":", [Date Opened]@row) - (FIND(" ", [Date Opened]@row) + 1))) < 12), 1, 0)

  • Jeremy Oesch
    Options

    @Paul Newcome

    Thank you Yes it a global tracker and thought it would follow my time settings. Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    The DISPLAY will be based on your time zone, but the back-end data is not unfortunately. This is a known issue.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!