Through the use of the **DayName** function, we can display the day name of the week for a particular date.

Today is Monday 13th February 2012.

What would be the result of creating the following formula in WebIntelligence?

=DayName(CurrentDate())

The answer is Monday. Today is Monday 13th February 2012.

Now, if I wanted to display the **previous****working*** day*, I couldn’t use the

**RelativeDate**function on it’s own. Remember we are looking for

**previous**

**working***and not*

**day***.*

**previous day**In order to do this, we need to check the current day and determine if the previous day is a weekend. If it is, we need to adjust the output accordingly.

We can develop this logic within WebIntelligence using a nested **IF** statement.

Let’s work through an example:

Create a new WebIntelligence report. If you have a CurrentDate object in your universe, we can use that. If you don’t, then bring back any object (*preferably one that doesn’t bring back many rows – we don’t actually want to use that value, but we do want to end up with a table*).

If you do not have a CurrentDate object, create the following formula in WebIntelligence:

=CurrentDate()

Note that the format of the date value returned depends on how the universe object has been set up or how your WebIntelligence setup is configured. In some cases it may be formatted as dd-Mmm-yyy or dd/mm/yyyy. If you want to use a specific format and you are using the **=CurrentDate()** formula in WebIntelligence, you can format the date using:

=FormatDate(CurrentDate();”dd-Mmm-yyyy”)

For the purposes of this article however, we will be using the default format.

Add a second column to your table. We will call this column ‘Current Day Name’ and we will use the **DayName** function to return the day of the week:

=DayName(CurrentDate())

Now to get the previous working date.

We need to check the value (Day Name) of the current date. If it is a Tuesday, Wednesday, Thursday, Friday or Saturday, we will be fine using the **RelativeDate** function, as the previous day to the above listed days is a working day. (I’m ignoring Bank Holidays, Festive Days, etc. That’s for a future topic!)

However, if we find that the current Day Name is a Sunday or a Monday, then using **RelativeDate** is going to retrieve a weekend day. We need to handle these two scenarios using a nested **IF** statement.

The statement is built up as follows:

If the current day name is a Monday, then subtract three days from the current date

If the current day name is a Sunday, then subtract two days from the current date

The formula is made up as follows:

=If(DayName(CurrentDate())=”Monday”;RelativeDate(CurrentDate();-3);If(DayName(CurrentDate())=”Sunday”;RelativeDate(CurrentDate();-2);RelativeDate(CurrentDate();-1)))

Lets break down this formula.

An IF expression has three components:

If(boolean_expr;value if true;value if false)

The boolean_expr part is the statement that we test.

In our case, we want to check if the **CurrentDate()** is a Monday.

If it is true that it is a Monday, we want to use the **RelativeDate** function to subtract 3 days from the **CurrentDate().**

If it is false, i.e. the current date is not a Monday, we need to perform a further check. This requires a second IF statement to perform this second check.

The second check asks if the **CurrentDate()** is a Sunday.

If it is true that it is a Sunday, we want to use the **RelativeDate** function to subtract 2 days from the **CurrentDate().**

If it is false, i.e. the current date is not a Sunday, and we know its not a Monday as we checked for that previously, then we know that the day is a Tuesday, Wednesday, Thursday, Friday or Saturday and hence we can safely use **RelativeDate** with a value of -1 to give us the previous working day.

The formula above displays the result in a date format. If we wanted to display the result as a Day Name only, we can tweak the formula a little to incorporate the **DayName()** function:

=If(DayName(CurrentDate())=”Monday”;DayName(RelativeDate(CurrentDate();-3));If(DayName(CurrentDate())=”Sunday”;DayName(RelativeDate(CurrentDate();-2));DayName(RelativeDate(CurrentDate();-1))))

So using this formula we should end up with the following result:

Current Day Previous Working Day

Monday Friday

Tuesday Monday

Wednesday Tuesday

Thursday Wednesday

Friday Thursday

Saturday Friday

Sunday Friday

June 26, 2012 at 2:09 pm

Very useful stuff, fixed the problem I was having. Thanks

June 26, 2012 at 7:29 pm

DC

Glad it helped you!

July 5, 2014 at 11:51 pm

I’m trying the same method on a sample exercise that i’m doing. The report needs to show shipping date according to the invoice day so if the

Invoice Date is Friday – shipping day should be Saturday

Invoice Date is Saturday – Wednesday.

Invoice Date is Sunday – Monday.

I used your formulate and replaced “invoice date” with “current date” and the formula checks out but in the column it displays the formula and not the days or dates. What am i doing wrong??