Why is my WEEKNUMBER returning two different weeks for the same day?

Mae Peterson

I have a function that counts if a form was filled out at least once a week. They are supposed to have from Sunday 12:01 am to Saturday 11:59pm to finish the form. I have 5 submissions for Sunday 01/05/2025 and two are reporting to the wrong week. How do I fix this?

Matt Johnson

Could you please post what the formula you are using is?

Mae Peterson

Mae Peterson

I found it easier to convert [Submission Date] to just a date using and then base my tracking off that.

Mae Peterson

Just kidding that didn't work Either

Mae Peterson

Okay everyone who finds this eventually.

I made a separate column that takes the automatic "Created on" date from smartsheets to just the date using the DATEONLY function. Problem with that is that it converts dates using GMT and I am not in that time zone I am in GMT-5 (US Eastern).

So to convert only adjusting time I used:

It works great and I currently have no issues.