Solved

# Calculate Date based on 3 factors

Posted on 2015-02-06
81 Views
Hi Experts

I have a challenge that I have just not been able to resolve.

I have a spreadsheet with 3 fields.  1---Frequency (Dly, Sat, Sun)  2---DOW (Day of the week) 3--Original Date.
What I need to do is to have a formula that would do this:  If DOW is a weekday and the freq is "dly" keep the original date, if DOW is a Sat and freq is a Sat keep original date, if DOW is a Sun and Freq is a Sun keep the original date but if the DOW is a DOW is a weekday but the Freq is a Sat or Sun then give me the Sat or Sun date for that same week.  If DOW is a Sat or Sun but the Freq is DLY then give me the "Friday" of that week.  I have been using a formula that I was hoping to modify to meet these rules but I can't get it to work.  =IF(C2="DLY",IF(G2="Sat",F2-1,IF(G2="Sun",F2,F2)),IF(C2="SAT",F2+WEEKDAY(F2+1)+1,F2-WEEKDAY(F2+1)+2))

Any help would be greatly appreciated.

Andy
0
Question by:spudmcc
• 4
• 3
• 2
• +3

LVL 24

Expert Comment

ID: 40593470
So far, we have:

1. If DOW is a weekday and the freq is "dly" keep the original date,
2. if DOW is a Sat and freq is a Sat keep original date,
3. if DOW is a Sun and Freq is a Sun keep the original date
4. but if the DOW is a DOW is a weekday but the Freq is a Sat or Sun then give me the Sat or Sun date for that same week.
5. If DOW is a Sat or Sun but the Freq is DLY then give me the "Friday" of that week.

Break it down by Day:

Weekdays
1. If DOW is a weekday and the freq is "dly" keep the original date,
4. but if the DOW is a DOW is a weekday but the Freq is a Sat or Sun then give me the Sat or Sun date for that same week.
Saturday
2. if DOW is a Sat and freq is a Sat keep original date,
5. If DOW is a Sat or Sun but the Freq is DLY then give me the "Friday" of that week.
Sunday
3. if DOW is a Sun and Freq is a Sun keep the original date
5. If DOW is a Sat or Sun but the Freq is DLY then give me the "Friday" of that week.

What happens if DOW is a Sat but freq is a Sun, or vice versa?

Also, what version of Excel do you use, and does your week start on a Monday?
0

LVL 43

Expert Comment

ID: 40593475
Can you upload a sample file with a few different scenarios for testing?
0

LVL 31

Expert Comment

ID: 40593478
Have you seen the CEILING/FLOOR functions before, its great for working with dates:

=CEILING(Date,7) will round a date up to the Saturday of that week

=FLOOR(Date,7) will round down to the Saturday of the previous week.

These might point you in the right direction, in the meantime I will take a look but have a question, is your DOW a number or a text value, ie 1 to 7 or Sunday to Saturday?

Thanks
Rob H
0

LVL 59

Expert Comment

ID: 40593488
I Assumed DOW is nothing but a calculated value basis of the original date, I build the formula using original date only to get the necesary result you are looking for...

Replace orignal date with the cell where you have original date...

=IF(OR(AND(C2="DLY",WEEKDAY(originaldate,2)<6),AND(OR(C2="Sun",C2="Sat"),WEEKDAY(originaldate,2)>=7)),originaldate,IF(AND(C2="DLY",WEEKDAY(originaldate,2)>5),originaldate+7-WEEKDAY(originaldate,2)-1,MAX(0,WEEKDAY(originaldate,3)-4)))

Also if you post your sample workbook it will be easy to view the situations and provide you a solution for the same..

Saurabh..
0

LVL 31

Expert Comment

ID: 40593489
Also, rather than nesting your IF statements, look at closing each one and going on to another, ie:

=IF(Criteria1,True,False)+IF(Criteria2,True,False)+IF(Criteria3,True,False)...etc

As you are working with dates, the FALSE in each case would be 0 and as Dates are dealt with as numbers, each scenario would generate a date or zero.

Lets assume Criteria1 gives 0, Criteria 2 gives Date, Criteria3 gives 0; adding them together, 0 + Date + 0, will give the date.

Thanks
Rob H
0

Author Comment

ID: 40593493
What happens if DOW is a Sat but freq is a Sun, or vice versa?
Answer:  Then the date should be that Sat of the same week.  Same with Sun.

Using Excel 2010

Rob--I haven't used the ceiling/floor functions.  I am very new to creating formulas and using functions.  My ability to incorporate them into a correct formula would truly be hit or miss.
0

Author Comment

ID: 40593500
Here is a small sample
EXAMPLE0206.xlsx
0

LVL 24

Accepted Solution

Phillip Burton earned 500 total points
ID: 40593509
Here you go:

=F2+
IF(G2="Sat",
IF(C2="DLY",-1,IF(C2="Sun",1,0)),
IF(G2="Sun",
IF(C2="DLY",-2,IF(C2="Sun",0,-1)),
IF(C2="Sat",5-WEEKDAY(F2,3),IF(C2="Sun",6-WEEKDAY(F2,3),0))))
0

Author Closing Comment

ID: 40593523
Brilliant!  It does exactly what I need it to do!

Thanks to you Phillip for your time and talent.  Thanks to all the Experts this morning!

A
0

LVL 4

Expert Comment

ID: 40593555
I see that you have closed this now, but figured I would post this as I have done the work.
Attached is a workbook showing how I approached the solution.
1. Set up a table with the relevant test rows
2. Use individual columns to tackle the challenge one step at a time (columns H to L)
3. Use these columns to determine the final answer (column M)
4. Merge the functions together into a single cell (column N)
5. Double-check that the single cell value matches the one derived from the multiple columns

Ideally you would also have an "Expected" column with hand-entered values to help validate that the value of column M (and therefore N) matches your expectations.

Might seem long-winded but can save a load of debugging and/or frustration that can result from trying to jump straight to the final answer.
3FactorDates.xlsx
0

Author Comment

ID: 40593571
I do very much appreciate your time and solution.  When I get a solution that works I do try and close it quickly so that others aren't continuing to work on it.  I guess it is a "darned if you do, darned if you don't" situation.

Again, thank you so much for your everything.  I have been using Experts-Exchange since the beginning and the amount of time and effort I have saved is not able to be measured.  I appreciate all of the Experts and the time and knowledge they devote to keeping this site at the top of my hit parade.
0

LVL 31

Expert Comment

ID: 40593584
Also, as an example of the CEILING function; it looks like your column B values were manual entries so can suggest a way of making these formula based, in cell B2 and copied down:

=IF(WEEKDAY(E2,1)=1,E2,CEILING(E2,7)+1)

In proper language - if E2 is A Sunday use E2 otherwise round E2 up to the Saturday of that week and then +1 makes the Sunday.

Thanks
Rob H
0

## Featured Post

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.