CODEWITHSUNDEEP
excel-formula
Frank M
Frank M

Reputation: 83

Using a dynamic single formula to calculate a date

This is a follow up to an earlier question.

@ForwardED I am trying to convert your original single static formula into a single dynamic formula.

Unfortunately my employer's filters will let me up certain things to a hyperlink, but will not let me download or view from the same site. I am also trying to come up with a formula for floating dates.

Upvotes: 0

Views: 77

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

Below is a copy of the expanded explanation I gave on the original question. I am not sure if you missed it or not. It deals with holidays that have a set date like Christmas, 25th of December of every year. However if it fall on a Saturday the time of work is the Friday and if it is on a Sunday the day off is the Monday.

Dealing with a holiday falling on a Saturday or Sunday

Again we need to refer to some cell in your spreadsheet with the year so I will again use Q10 as the example and we will assume a date of 2014/10/24.

=IF(WEEKDAY(DATE(YEAR(Q10),12,25))=7,DATE(YEAR(Q10),12,24),IF(WEEKDAY(DATE(YEAR(Q10),12,25))=1,DATE(YEAR(Q10),12,26),DATE(YEAR(Q10),12,25)))

The formula checks first if the weekday is a Saturday. We do this using a function that will return the day of the week See step 2) from the original question. It is this part from the equation above:

WEEKDAY(DATE(YEAR(Q10),12,25))

It will return a single integer 1 through 7 corresponding to the day of the week the date function results in, in this case. If its a 1 we known its Sunday, if its 7 we know its Saturday. So the check for Saturday is:

WEEKDAY(DATE(YEAR(Q10),12,25))=7

If WEEKDAY()=7 is true then we provides the date of the day before which is really just subtracting 1 from the date we were looking at. We use this part of the formula to calculate that:

DATE(YEAR(Q10),12,24)

notice how I changed the day from 25 to 24. An alternate way would be to recycle our date and make the computer do one more calculation using this formula:

DATE(YEAR(Q10),12,25)-1
or
DATE(YEAR(Q10),12,25-1)

That all sits in the TRUE portion of the if statement. so if the date does not fall on Saturday then we wind up in the FLASE portion of the IF statement. Here we check with a second IF for the date falling on a Sunday. we use the same theory and process as we did for the Saturday check.

IF(WEEKDAY(DATE(YEAR(Q10),12,25))=1,DATE(YEAR(Q10),12,26),DATE(YEAR(Q10),12,25))

Placing an IF statement inside an IF statement is commonly referred to as "nesting". This whole IF statement happens in the FALSE portion of the previous IF that checked to see if it was Saturday. This time we checked for Sunday:

WEEKDAY(DATE(YEAR(Q10),12,25))=1

When this is true, then we need to increase the date by 1 day instead of decreasing it like was done for Saturday:

DATE(YEAR(Q10),12,26)
or
DATE(YEAR(Q10),12,25)+1
or
DATE(YEAR(Q10),12,25+1)

So that was the true portion of the Sunday check. Logically speaking the only way to get to the FALSE portion of this nested IF statement is to fail the Saturday check and then fail the Sunday check. Which means you do not need to go through and check if is the WEEKDAY comes out as 2, 3, 4, 5 or 6! Its one of those by the process of eliminating Sunday and Saturday (1 and 7). And if the date falls on Monday-Friday we dont need to change the date and can leave it just as is:

DATE(YEAR(Q10),12,25)

And I realized I did not explain how the date function works, though I think I tried to in one of the previous questions...regardless! DATE(arg1,arg2,arg3) requires three different arguments as integers or other functions that return integers.

  • arg1 is the year so 2014, 1995, 1965 are all acceptable integers. Also we could use YEAR(Q10), where the cell Q10 holds the date of 2014/10/24. In this case YEAR(Q10) would return 2014.

  • arg2 is the month and needs to be an integer in the range of 1 to 12. Again you can always use a formula that returns an integer in that range as well such as MONTH(Q10) which from our previous value of Q10 would return 10.

  • arg3 is day and similar to the above it needs to be an integer. A formula such as DAY(Q10) would return a value of 24.

What this means is if we know what day a holiday is on we can force it to a date by supplying a set month and day, and letting the year be determined by a formula that supplies the year you are interested in. So if you look at the last formula you can see we fixed the month at 12 and the day at 25. They year will be determine from the year of the date supplied in cell Q10.

Upvotes: 1

Related Questions