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
Solved

Calculate Date based on 3 factors

Posted on 2015-02-06
12
88 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 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

856 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