Formula for days of week a ticket was submitted

Jeremy Oesch

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.

JamesB

@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

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)

JamesB

@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

@JamesB

It works great thank you very much!

Jeremy Oesch

@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

@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

@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

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

@Paul Newcome

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

Paul Newcome

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