Thread: Catching a running date (Excel 2003)

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

2. Re: Catching a running date (Excel 2003)

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

Steve

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

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

5. Re: Catching a running date (Excel 2003)

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

Steve

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

9. Re: Catching a running date (Excel 2003)

Check carefully - Steve's formula is correct.

I have attached a very small sample.

10. 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
•