Modifying date formulas

[Steve]

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?

 

[Ron Rosenfeld]

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?

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

 

[JoeU2004]

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

 

[JoeU2004]

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

 

[Steve]

Thank you both, all sorted and lessons learned.

 

[Ron Rosenfeld]

Thank you both, all sorted and lessons learned.

Glad to help. Thanks for the feedback.
--ron