Solved

Calculate Date based on 3 factors

Posted on 2015-02-06
12
85 Views
Last Modified: 2015-02-06
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
Comment
Question by:spudmcc
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
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

by:Saqib Husain, Syed
ID: 40593475
Can you upload a sample file with a few different scenarios for testing?
0
 
LVL 32

Expert Comment

by:Rob Henson
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
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 32

Expert Comment

by:Rob Henson
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

by:spudmcc
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

by:spudmcc
ID: 40593500
Here is a small sample
EXAMPLE0206.xlsx
0
 
LVL 24

Accepted Solution

by:
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))))

Open in new window

0
 

Author Closing Comment

by:spudmcc
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

by:scsyme
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

by:spudmcc
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 32

Expert Comment

by:Rob Henson
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question