How can I accurately identify the day of the week in my survey calculations in Smartsheet?

Chad_R

I am reaching out to seek your assistance regarding an issue I'm encountering while calculating the number of surveys completed on each day of the week (Monday-Sunday).

Despite various attempts to identify the correct day of the week using functions like DAY, WEEKDAY, and DATEONLY, I am consistently facing discrepancies where the days seem to transition at 6 PM, resulting in inaccurate calculations. I have attached an image for reference.

I have verified the Time Zone settings and how the Work Days are defined, but the issue persists. At this point, I am open to any recommendations or alternative methods you may suggest to resolve this issue effectively.

Any assistance in resolving this matter would be greatly appreciated. Please feel free to reach out if you need any further clarification or information.

Chad_R

Ok Perfect. I did not go with your solution but your impute inspired the solution that I went with. I converted the DATE ENTERED to text and then back to a DATE with this formula.

=DATE(VALUE("20" + MID([Date Entered Helper]@row, 7, 2)),VALUE(LEFT([Date Entered Helper]@row, 2)),VALUE(MID([Date Entered Helper]@row, 4, 2)))

Once the date no longer had the time, this allowed the WEEKDAY function to not progress to the next day at 6pm

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

Now my Mondays are Monday all day long. So THANK YOUR!!!!

I would NOT have gotten here with out your generous help & impute. I so appreciate this!

I don't see the benefit in having a function that advances to the next day after 6 pm... This may be helpful to some but if you have such a function you should at least also have a normal function that EASILY counter acted the ridiculous one...lol.

THANKS AGAIN!

AravindGP

Hi @Chad_R

Smartsheet's time cut off seems to be based on GMT for Day, Weekday, Dateonly functions, which is the reason you see a difference. I have a longer workaround for it, but it takes time.

You can split the date in another date column using the formula below. But note that the system doesn't still accept the value as a date. So, to get the day, you will need to maintain a separate sheet with all of the days in a year and a column next to it showing the week day, i.e., Monday, Tuesday, etc. You can then use an INDEX/MATCH formula to get the days populated back in your main sheet and use that in your metric calculations.

=LEFT([Date Entered]@row, 2) + "/" + MID([Date Entered]@row, 4, 2) + "/" + MID([Date Entered]@row, 7, 2)

Chad_R

Ok Perfect. I did not go with your solution but your impute inspired the solution that I went with. I converted the DATE ENTERED to text and then back to a DATE with this formula.

=DATE(VALUE("20" + MID([Date Entered Helper]@row, 7, 2)),VALUE(LEFT([Date Entered Helper]@row, 2)),VALUE(MID([Date Entered Helper]@row, 4, 2)))

Once the date no longer had the time, this allowed the WEEKDAY function to not progress to the next day at 6pm

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

Now my Mondays are Monday all day long. So THANK YOUR!!!!

I would NOT have gotten here with out your generous help & impute. I so appreciate this!

I don't see the benefit in having a function that advances to the next day after 6 pm... This may be helpful to some but if you have such a function you should at least also have a normal function that EASILY counter acted the ridiculous one...lol.

THANKS AGAIN!