Re: Modifying date formulas

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"Steve" <stevebakerj@xxxxxxxxxxx> 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" <stevebakerj@xxxxxxxxxxx> wrote in message news:lO2dnTaL1-fSZP_XnZ2dnUVZ8tadnZ2d@xxxxxxxxx
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

.

Relevant Pages

  • Re: Modifying date formulas
    ... I forgot to divide by 7 to compute weeks. ... This works fine however now I want to modify the ... 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 ...
    (microsoft.public.excel.newusers)
  • Re: OT: Snits argument preserved - act 2
    ... is what John would have said if he subscribed to Steve's ... maybe - Steve will seek the help he needs. ... people believe him or, at the least, question those he targets. ... Macs are Macs and Apple is still making and selling Macs ...
    (comp.sys.mac.advocacy)
  • Re: VT Shooter, A Mac user.... (maybe, maybe not)
    ... Snit wrote: ... That is what Steve does. ... John correctly points out Steve's trolling. ... Steve has admitted he "targets" me... ...
    (comp.sys.mac.advocacy)
  • Re: NO WARNING: PBR drop targets not total POS
    ... I had this same problem with some of the BH drop targets I bought from Steve ... to have a rush order put in to make you happy. ... Steve is new and used the wrong paint. ...
    (rec.games.pinball)
  • Re: updating data from one table to insert into a scond table
    ... You can always add and modify tables, ... Most people here have a common belief that the newsgroups are for *free exchange of information*. ... But Steve is a notorious job hunter in these groups, always trying to sell his services. ... Read how to complain at http://home.tiscali.nl/arracom/whoissteve.html ...
    (microsoft.public.access.gettingstarted)