Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Catching a running date (Excel 2003)

    Hi

    I have a file that contain expired dates. I am trying to find a formula in col H that can update the new expired date. Currently I am using =A20-TODAY()

    Let say I have in col A the first expired date is 10 Jan 2008, and on 5th Jan, it will show that number of days before expire date is 5 in col H, this work well.

    Come 10 Jan 2008, a member renew it's member for 1 month and hence the expire date have been extended to 10 Feb 2008 which will input in col B , the above formula will shows -1 in col H. I am have difficulty creating the formula to ignore the old date in col A and read the new date in col B if members have renew and a
    new expiry date have been input. I need to amend the formula to point to the correct cell every time there is a renew.

    What will be the formula to capture this changes in dates as this will go on for the rest of the year.

    TIA

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Catching a running date (Excel 2003)

    How about:
    =IF(A20<TODAY(),IF(B20="","Expired",B20-TODAY()),A20-TODAY())

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Catching a running date (Excel 2003)

    Hi Steve,

    thanks for looking into this, It seem to work as expected. Let me try to adjust the formula to run for the next 12 month, and I will post back if I got stuck

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Catching a running date (Excel 2003)

    Hi

    I am stuck with adding in more dates across the row, eg. in A20, I have 15/11/07, B20, I have 15/12/07, C20, I have 15/01/07 and D20, I have 15/02/08
    to this :
    =IF(A20<TODAY(),IF(B20="","Expired",B20-TODAY()),A20-TODAY()) which can read the latest maturity date, ie D20.

    I intend to create this formula for the 12 month period and I have tried adding "&" and "+" but can't get it to work. I have also tried Datedif.

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Catching a running date (Excel 2003)

    How about something simple like:

    =IF(MAX(A20:G20)<TODAY(),"Expired",MAX(A20:G20)-TODAY())

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Catching a running date (Excel 2003)

    If you want to be able to enter up to 12 dates (one for each month), you shouldn't put the formula in column H - there are only 7 columns to the left of H. Use column M or further to the right.

    Steve's formula expanded to 12 entries would be
    <code>
    =IF(MAX(A20:L20)<TODAY(),"Expired",MAX(A20:L20)-TODAY())</code>

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Catching a running date (Excel 2003)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>Good Catch. I kept the formula in H20 and didn't think at all about the 12 dates...

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Catching a running date (Excel 2003)

    Hi Steve,

    I am not sure if I did not put the formula correctly, I have an expire date on 15/02/2008 and if I count the expire date from today, the formula
    should give me something like 14 but I get "Expired". How does this formula works?

    Does this works if in between those cells with dates there are other values instead of dates, eg 1M

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Catching a running date (Excel 2003)

    Check carefully - Steve's formula is correct.

    I have attached a very small sample.
    Attached Files Attached Files

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Catching a running date (Excel 2003)

    If the other cells have text they will have no effect. If they are numbers which are less than about 39000 they would not iinterfere.

    If they can be numbers that are greater than the serial dates used, you will have to create the formulas to ignore those cells, like (to ignore D20): MAX(A20:C20,E20:G20)

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •