Hello, We have multiple sheets for client time and expense tracking. Our company policy is that time and/or expenses MUST be entered the day of the time worked or expense charged. The reasons for this requirement: * Our sheets are linked to Account Manager and client dashboards, project trackers and budget reports to provide as close to 'real time' a view as possible * In the past we have had people 'sand bag' hours for an entire week, enter them on Friday afternoon when clients were reviewing status, budget and suddenly see their projects, spend balloon by 20, 30, 40 or more hours right before their eyes. In some cases these caused budgets to be exceeded. These have not been fun follow-up conversations from our clients * Our billing cycles for clients are 1st to 15th and 16th to end of month. We have some people that will back date, entering their time on 2/1 or 2/2 for example, but filling in the 'Date Worked' on the form for 1/31. Often this is after the invoice to the client has been processed and sent However, we are having violators. Some people just don't seem to be able to follow rules, not matter how many times you explain why, reason with, threaten to terminate or actually terminate their co-workers for these violations. Is there a mechanism I can create using "todays date" that will: * Not allow a form entry to be submitted if back dating AND; * Notify me if someone attempts to back date an entry Any suggestions would be appreciated.

Restrict form entry

Hello,

We have multiple sheets for client time and expense tracking. Our company policy is that time and/or expenses MUST be entered the day of the time worked or expense charged. The reasons for this requirement:

Our sheets are linked to Account Manager and client dashboards, project trackers and budget reports to provide as close to 'real time' a view as possible
In the past we have had people 'sand bag' hours for an entire week, enter them on Friday afternoon when clients were reviewing status, budget and suddenly see their projects, spend balloon by 20, 30, 40 or more hours right before their eyes. In some cases these caused budgets to be exceeded. These have not been fun follow-up conversations from our clients
Our billing cycles for clients are 1st to 15th and 16th to end of month. We have some people that will back date, entering their time on 2/1 or 2/2 for example, but filling in the 'Date Worked' on the form for 1/31. Often this is after the invoice to the client has been processed and sent

However, we are having violators. Some people just don't seem to be able to follow rules, not matter how many times you explain why, reason with, threaten to terminate or actually terminate their co-workers for these violations.

Is there a mechanism I can create using "todays date" that will:

Not allow a form entry to be submitted if back dating AND;
Notify me if someone attempts to back date an entry

Any suggestions would be appreciated.

Find more posts tagged with

Accepted answers

All comments

Sean Morgan

Hello @JB ,

I'm sorry to hear the issues you're facing with having users submit entries on time.

For "Not allow a form entry to be submitted if back dating", I don't think this would be possible as the date fields are not dynamic enough to have logic such as IF(Date <Today, block field).

I was able to create a solution that may help:

In your Sheet, you could add a Flag Column with the following Formula to identify a back dated entry:

=IF(MONTH([Submitted Date]@row) < MONTH(TODAY()), 1, 0)

In the following screenshot, you can see that Test 2 is flagged. Rather than using the last part of the above Formula "TODAY()", I referenced a different cell that had a date in March, so that you could see if we were currently in March, and something was submitted in February, you can see how the Formula would work:

From here, you could then build an Alert that is trigger by the Flag turning red, and to send you an Alert.

You could also add additional Columns to identify if users are submitting their hours on track with the correct month, and within either the 1st or 2nd Cycle of the Month. Please note the following is a rough example and could be shortened, this is just for demonstration purposes:

=IF(AND(OR(DAY([Submitted Date]@row) = 1, DAY([Submitted Date]@row) = 2, DAY([Submitted Date]@row) = 3, DAY([Submitted Date]@row) = 4, DAY([Submitted Date]@row) = 5, DAY([Submitted Date]@row) = 6, DAY([Submitted Date]@row) = 7, DAY([Submitted Date]@row) = 8, DAY([Submitted Date]@row) = 9, DAY([Submitted Date]@row) = 10, DAY([Submitted Date]@row) = 11, DAY([Submitted Date]@row) = 12, DAY([Submitted Date]@row) = 13, DAY([Submitted Date]@row) = 14, DAY([Submitted Date]@row) = 15), MONTH([Submitted Date]@row) = MONTH(TODAY())), 1, 0)

The above is currently within the 1st Cycle Column. You could easily make this and the first Formula, Column Formulas, and for the 2nd Cycle Column, you could change the above numbers to match the days for the second cycle.

Let me know if you have any questions!

Regards

Sean

unknown

This content has been removed.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.