AZ Time Zone Issues when form is submitted

beaglemomma

Working out of AZ pacific time, I have analysts submit forms at 9:00pm Saturday night and the form is counting the audit for the following week. Example: Week 44 audit is submitted on or after 9:00pm Saturday night, form is counted on Week 45 section, and not for Week 44. All the analysts personal settings are set to AZ time frame and their laptops are set to AZ time zone (No daylight savings) How can we solve this problem?

Genevieve P.

Hi @beaglemomma

How are you tracking the date of the submission, and how are you finding the "week" number? Are you using formulas?

What I would suggest doing in this instance is to have a Record a Date workflow add the date as a new row is added (see: Set the current date with the Record a Date action).

Then you can use this column to generate the Week Number, using the WEEKNUMBER function.

="Week " + WEEKNUMBER([Recorded Date]@row)

Keep in mind that this function uses Monday as the start of the week, so a form submission coming in on Sunday would still be in the previous week.

Cheers,

Genevieve

beaglemomma

The date of submission is tracked when the compliance analyst hits submit on the form. We are using formulas from our tracker. We have compliance analyst's that do Sunday audits. So this would not be a formula I can change to "Sunday"?

Genevieve P.

Hi @beaglemomma

I would still use the same set-up, but we can adjust the formula to account for a Sunday start date. The WEEKNUMBER function starts on Monday, but we can use the WEEKDAY function and an IF statement to check when the Recorded date came in:

="Week " + IF(WEEKDAY([Recorded Date]@row) = 1, WEEKNUMBER([Recorded Date]@row) - 1, WEEKNUMBER([Recorded Date]@row))

This says, if the Weekday in the Recorded Date cell is Sunday, then subtract 1 from the WeekNumber (moving it back to the prior week). Otherwise, return the current WeekNumber.

Let me know if the formula works for you!

Cheers,

Genevieve