Solved

Calculate Date based on 3 factors

Posted on 2015-02-06
12
83 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now