Question

Create a data validation rule for a spreadsheet column that only allows entries that are dates within the last 90 days and fall on a weekday.

Accepted Answer

To create a data validation rule for a spreadsheet column that only allows entries that are dates within the last 90 days and fall on a weekday, you would use a custom formula. The specific formula to enter into the data validation settings for a cell like A1, assuming A1 is the first cell in the column where you are applying the rule, is: =AND(A1>=TODAY()-90, A1<=TODAY(), WEEKDAY(A1,2)<=5). A data validation rule is a feature in spreadsheet software that allows you to control what kind of data can be entered into a cell or range of cells. If an entry does not meet the specified criteria, the software will prevent the entry and often show an error message. When applying this rule to a column, select the column or the specific range of cells where you want the rule to apply. Then, navigate to the data validation feature, choose 'Custom' for the validation criteria, and input the formula. Let's break down the formula: The entire formula is wrapped in the AND function. The AND function is a logical function that returns TRUE only if all of its conditions are met. If even one condition is not met, it returns FALSE. For data validation, if the formula evaluates to TRUE, the entry is allowed; if FALSE, it is not. The first part, A1>=TODAY()-90, checks if the date entered in cell A1 is greater than or equal to the date 90 days ago. TODAY() is a spreadsheet function that automatically returns the current date according to the system clock. It updates whenever the spreadsheet recalculates. So, TODAY()-90 calculates the date exactly 90 days prior to the current date. The second part, A1<=TODAY(), checks if the date entered in cell A1 is less than or equal to the current date. Together, A1>=TODAY()-90 and A1<=TODAY() ensure that the entered date falls strictly within the last 90 days, including today's date. The third part, WEEKDAY(A1,2)<=5, ensures that the entered date is a weekday. The WEEKDAY function returns a number representing the day of the week for a given date. The '2' as the second argument tells the WEEKDAY function to consider Monday as 1, Tuesday as 2, and so on, up to Sunday as 7. Therefore, if the WEEKDAY function returns a number less than or equal to 5, it means the date falls on a Monday, Tuesday, Wednesday, Thursday, or Friday. If it returns 6 (Saturday) or 7 (Sunday), the condition is not met. For an entry to be permitted, it must simultaneously satisfy all three conditions: it must be on or after 90 days ago, on or before today, and be a weekday.

Home → All Courses → Engineering and Technology Courses → Google Data Analytics Professional Certificate → Flashcard

Create a data validation rule for a spreadsheet column that only allows entries that are dates within the last 90 days and fall on a weekday.