Hi Everyone,
My name is Dave and I have been using Excel for a decent amount of time and am currently wresting with my nemesis- date calculations/ date formulas. I am aware that Excel uses serial numbers with dates and times. Please allow me to describe my issues and pardon any long-windedness. The problem is slightly deep, I've gone through a few steps to attempt to solve (which I will explain) and all-in-all takes some time to describe.
I’m creating a schedule working backwards from a target date. I had entered in the target date in 3 cells – Year, month and day. From this I’m trying to schedule my milestones subtracting numbers of days from the target date. Here’s the formula that I started with: =DATE(year,month,day)-# of days. This is working great but I can’t have the end date be a weekend so I wanted to add in an “IF” for if the value equals a Saturday or Sunday, then subtract 1 or 2 days respectively. I tried all sorts of “IF” formulas and I can’t seem to get it right.
I’m not even sure if the initial formula is the most appropriate but I tried a lot of options – I tried the WORKDAY formula which was great but it removed all my weekends and I wanted to start with all days and then subtract 1 or 2 days appropriately if it happened to be a week end date.
That said after further analysis I actually do want to count in ever day of the week, but the end date needs to be on a week day. I don’t need to count the days between 2 dates, I need to get a date by adding or subtracting days. So I figured out the basic subtraction of days (I went way too complicated by first go around) and then I created a second column with the “IF” formula. When I try to combine the 2 formulas, then I get a circular reference error. See below:
Here are the cells with the end data. I wanted the date to be 16 days prior to my target date (5/16). Since 16 days prior to May 16, falls on a Saturday (F6), I needed the formula to recognize it can’t be on a Saturday (or Sunday for that matter) and to subtract the appropriate amount of days to get back onto a Friday (F7).
Cell F6 Cell F7
Saturday, April 30, 2011 Friday, April 29, 2011
Here are the formulas:
=$F$20-16=IF(ISNUMBER(SEARCH("Saturday",F6)),0,(F6-1))
*F20 is the target date of May 16
How do I combine these two formulas without having a circular reference issue (does it need to be an “IF/THEN” scenario or will I always need to have 2 columns?)?
So am I going down the right path? Would integrating the NETWORKDAY function be a better option? Should I restructure the spreadsheet to make it more formula friendly? Any help would be most appreciated....
Thanks!!!
-Dave
My name is Dave and I have been using Excel for a decent amount of time and am currently wresting with my nemesis- date calculations/ date formulas. I am aware that Excel uses serial numbers with dates and times. Please allow me to describe my issues and pardon any long-windedness. The problem is slightly deep, I've gone through a few steps to attempt to solve (which I will explain) and all-in-all takes some time to describe.
I’m creating a schedule working backwards from a target date. I had entered in the target date in 3 cells – Year, month and day. From this I’m trying to schedule my milestones subtracting numbers of days from the target date. Here’s the formula that I started with: =DATE(year,month,day)-# of days. This is working great but I can’t have the end date be a weekend so I wanted to add in an “IF” for if the value equals a Saturday or Sunday, then subtract 1 or 2 days respectively. I tried all sorts of “IF” formulas and I can’t seem to get it right.
I’m not even sure if the initial formula is the most appropriate but I tried a lot of options – I tried the WORKDAY formula which was great but it removed all my weekends and I wanted to start with all days and then subtract 1 or 2 days appropriately if it happened to be a week end date.
That said after further analysis I actually do want to count in ever day of the week, but the end date needs to be on a week day. I don’t need to count the days between 2 dates, I need to get a date by adding or subtracting days. So I figured out the basic subtraction of days (I went way too complicated by first go around) and then I created a second column with the “IF” formula. When I try to combine the 2 formulas, then I get a circular reference error. See below:
Here are the cells with the end data. I wanted the date to be 16 days prior to my target date (5/16). Since 16 days prior to May 16, falls on a Saturday (F6), I needed the formula to recognize it can’t be on a Saturday (or Sunday for that matter) and to subtract the appropriate amount of days to get back onto a Friday (F7).
Cell F6 Cell F7
Saturday, April 30, 2011 Friday, April 29, 2011
Here are the formulas:
=$F$20-16=IF(ISNUMBER(SEARCH("Saturday",F6)),0,(F6-1))
*F20 is the target date of May 16
How do I combine these two formulas without having a circular reference issue (does it need to be an “IF/THEN” scenario or will I always need to have 2 columns?)?
So am I going down the right path? Would integrating the NETWORKDAY function be a better option? Should I restructure the spreadsheet to make it more formula friendly? Any help would be most appreciated....
Thanks!!!
-Dave
