Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Calculate Date based on 3 factors

Posted on 2015-02-06
12
Medium Priority
?
92 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 33

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 33

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 2000 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 33

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

610 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