Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Modifying date formulas
Hi,
I have built a spread sheet and one of the things I'm measuring is targets for completion of an event, the target is measured in weeks from an entered start date to an entered completion date, I have used the following formula, based on K2 as the start date and Z2 as the completion date: =DATEDIF($K2,$Z2,"d")/7 This works fine however now I want to modify the formula to say that if the start date entered is equal to or before (<=) 31/04/2009 (39934) then use 31/04/2009 (39934) as the start date if its greater than () that then use the date as entered in K2, which gives: =IF($K2<=39934, 39934, $K2) So the question is how do I combine them? -- Steve |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Modifying date formulas
On Sun, 19 Jul 2009 11:45:33 +0100, Steve wrote:
Hi, I have built a spread sheet and one of the things I'm measuring is targets for completion of an event, the target is measured in weeks from an entered start date to an entered completion date, I have used the following formula, based on K2 as the start date and Z2 as the completion date: =DATEDIF($K2,$Z2,"d")/7 This works fine however now I want to modify the formula to say that if the start date entered is equal to or before (<=) 31/04/2009 (39934) then use 31/04/2009 (39934) as the start date if its greater than () that then use the date as entered in K2, which gives: =IF($K2<=39934, 39934, $K2) So the question is how do I combine them? -- Steve Comments. Excel stores dates as integers with 1= 1 Jan 1900 (or 2 Jan 1904) 1. Your original formula can be simplified: =(EndDate-StartDate)/7 2. To set a particular StartDate as being the earliest: =(EndDate-MAX(EarliestStartDate,StartDate))/7 --ron |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Modifying date formulas
"Steve" wrote:
=DATEDIF($K2,$Z2,"d")/7 This works fine however now I want to modify the formula to say that if the start date entered is equal to or before (<=) 31/04/2009 (39934) then use 31/04/2009 (39934) as the start date if its greater than () that then use the date as entered in K2 First, no need to use DATEDIF to compute difference in days. Second, no need to refer to dates by serial number; in fact, I would say it is a bad idea (impossible to relate to). Try: =$Z2 - max($K2, date(2009,4,31)) Alternatively, you can write either of the following. But I think they are deprecated because they depend on your computer's regional settings. =$Z2 - max($K2, datevalue("31/04/2009")) =$Z2 - max($K2, --"31/04/2009") ----- original message ----- "Steve" wrote in message ... Hi, I have built a spread sheet and one of the things I'm measuring is targets for completion of an event, the target is measured in weeks from an entered start date to an entered completion date, I have used the following formula, based on K2 as the start date and Z2 as the completion date: =DATEDIF($K2,$Z2,"d")/7 This works fine however now I want to modify the formula to say that if the start date entered is equal to or before (<=) 31/04/2009 (39934) then use 31/04/2009 (39934) as the start date if its greater than () that then use the date as entered in K2, which gives: =IF($K2<=39934, 39934, $K2) So the question is how do I combine them? -- Steve |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Modifying date formulas
Errata....
I forgot to divide by 7 to compute weeks. That should be obvious. But.... =($Z2 - max($K2, date(2009,4,31))) / 7 ----- original message ----- "JoeU2004" wrote in message ... "Steve" wrote: =DATEDIF($K2,$Z2,"d")/7 This works fine however now I want to modify the formula to say that if the start date entered is equal to or before (<=) 31/04/2009 (39934) then use 31/04/2009 (39934) as the start date if its greater than () that then use the date as entered in K2 First, no need to use DATEDIF to compute difference in days. Second, no need to refer to dates by serial number; in fact, I would say it is a bad idea (impossible to relate to). Try: =$Z2 - max($K2, date(2009,4,31)) Alternatively, you can write either of the following. But I think they are deprecated because they depend on your computer's regional settings. =$Z2 - max($K2, datevalue("31/04/2009")) =$Z2 - max($K2, --"31/04/2009") ----- original message ----- "Steve" wrote in message ... Hi, I have built a spread sheet and one of the things I'm measuring is targets for completion of an event, the target is measured in weeks from an entered start date to an entered completion date, I have used the following formula, based on K2 as the start date and Z2 as the completion date: =DATEDIF($K2,$Z2,"d")/7 This works fine however now I want to modify the formula to say that if the start date entered is equal to or before (<=) 31/04/2009 (39934) then use 31/04/2009 (39934) as the start date if its greater than () that then use the date as entered in K2, which gives: =IF($K2<=39934, 39934, $K2) So the question is how do I combine them? -- Steve |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Modifying date formulas
Thank you both, all sorted and lessons learned. -- Steve Beware of all enterprises that require new clothes. - Henry David Thoreau |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Modifying date formulas
On Mon, 20 Jul 2009 07:55:58 +0100, Steve wrote:
Thank you both, all sorted and lessons learned. Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting Cells from changing or modifying long formulas | Excel Discussion (Misc queries) | |||
need help modifying a complex column of formulas | Excel Discussion (Misc queries) | |||
Modifying data in excel containing 4000+ formulas | Excel Worksheet Functions | |||
Modifying a footer date | Excel Discussion (Misc queries) | |||
Modifying sumif formulas. | Excel Worksheet Functions |