Formula 10

Operators/Functions Used

If-Then-Else, Not, DayOfWeek, Make Range (to), In Range (in), Parentheses ()

Formula Purpose

A seven day per week customer service department logs all calls and stores the records in a database. As an aid in scheduling weekend staff, management wants to flag the weekend calls so they stand out in the call report.

Formula

If Not(DayOfWeek({file.CALL DATE}) in 2 to 6) Then

    "Weekend"

Else 

    ""

Result

Day of Week	#	Flag
Sunday	1	Weekend
Monday	2
Tuesday	3
Wednesday	4
Thursday	5
Friday	6
Saturday	7	Weekend

Explanation

• The formula uses the If-Then-Else operator to say, "If the day of the week is not a weekday, then print "Weekend", otherwise (Else) print nothing (as indicated by the empty text string "").
• The If part of the formula sets up the condition, "If the day of the week is not a weekday".
• The DayOfWeek function evaluates {file.CALL DATE} and returns a number from 1 to 7 (Sunday being 1, Saturday being 7) to indicate the day of the week on which a call was made.
• In 2 to 6 uses the In Range function (in) and the Make Range function (to) to determine if the day of the week the call was made was in the range 2 to 6 (Monday to Friday).
• The Not function negates the expression that follows.
• Without the Not function preceding the expression, the If expression reads "If the day of the week number indicates the call date was a weekday."
• With the Not function, the If expression reads, "If the day of the week number indicates the call date was not a weekday."
• If the call date is not a weekday, the If expression is satisfied, thus triggering the Then consequence.
• "Weekend" tells the program that when the If condition is satisfied, print the word "Weekend".
• If the call date is a weekday, the If condition is not satisfied, thus triggering the Else consequence.
• "" tells the program to print nothing if the If condition is not satisfied.

Related topics

Formulas In Action