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

Question

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 · Accepted Answer

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!