countdown dates by month

I have a table in which I want to have the day of the month automatically
update whenever I open the spreadsheet.

Each month has its own row, and as the month ends, the days gone column
should stay @ the maximum number of days for that month, and the next row
start to be filled in (table below)

Any idea on how I can use the today() function and maybe the if() function,
or WHATEVER to figure this out?

Countdown
% # Days Days Gone
Jul 100% 30 30
Aug 100% 31 31
Sep 100% 30 30
Oct 100% 31 31
Nov 93% 30 28
Dec 0% 31
Jan 0% 30
Feb 0% 31
Mar 0% 30
Apr 0% 30
May 0% 31
Jun 0% 30

If you've got a header row 1 with the data beginning in row 2, this formula,
entered in D2, ought to do it:
=IF(MATCH(TEXT(TODAY(),"Mmm"),A:A,0)<ROW(),"",IF(TEXT(TODAY(),"Mmm")=A2,DAY(TODAY()),C2)).
A couple points. First, this only allows 12 months and they must all be in
the same three-letter format. Second, the 'days gone' in the current month
will include the current day (on 12/1, the row for Dec will indicate 1 day
gone). The basic logic is to ask whether the current month appears in a row
above the one we're evaluating. If so, this is a future month so the days
gone is left blank. Otherwise: if this row represents the current month,
enter the day; otherwise copy the # days from column C.
HTH. --Bruce

"shopaholic" wrote:

> I have a table in which I want to have the day of the month automatically
> update whenever I open the spreadsheet.
>
> Each month has its own row, and as the month ends, the days gone column
> should stay @ the maximum number of days for that month, and the next row
> start to be filled in (table below)
>
> Any idea on how I can use the today() function and maybe the if() function,
> or WHATEVER to figure this out?
>
> Countdown
> % # Days Days Gone
> Jul 100% 30 30
> Aug 100% 31 31
> Sep 100% 30 30
> Oct 100% 31 31
> Nov 93% 30 28
> Dec 0% 31
> Jan 0% 30
> Feb 0% 31
> Mar 0% 30
> Apr 0% 30
> May 0% 31
> Jun 0% 30
>
>

Hi

Row1: column headers (A1="Month", B1="Percent", C1="Days")
A2 - enter starting month as 1st of month (like 01.06.2005).
A3=DATE(YEAR($A$2),MONTH($A$2)+ROW()-2,1)
Format cells A2:A3 in any valid date format - my advice is to use a custom
format like "mmm yyyy"
C3=CHOOSE(SIGN($A3-DATE(YEAR(TODAY()),MONTH(TODAY()),1))+2,DAY(DATE(YEAR($A$2),MONTH($A$2)+ROW()-1,0)),DAY(TODAY()),0)
Format C3 as Number with 0 decimals
B3=$C3/DAY(DATE(YEAR($A3),MONTH($A3)+1,0))
Format B3 as percentage
Copy B3:C3 to B2:C2
Copy A3:C3 down for as much rows as you need


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"shopaholic" <shopaholic@discussions.microsoft.com> wrote in message
news:017F97BD-EE1F-4488-8866-75A3B9CE34B9@microsoft.com...
>I have a table in which I want to have the day of the month automatically
> update whenever I open the spreadsheet.
>
> Each month has its own row, and as the month ends, the days gone column
> should stay @ the maximum number of days for that month, and the next row
> start to be filled in (table below)
>
> Any idea on how I can use the today() function and maybe the if()
> function,
> or WHATEVER to figure this out?
>
> Countdown
> % # Days Days Gone
> Jul 100% 30 30
> Aug 100% 31 31
> Sep 100% 30 30
> Oct 100% 31 31
> Nov 93% 30 28
> Dec 0% 31
> Jan 0% 30
> Feb 0% 31
> Mar 0% 30
> Apr 0% 30
> May 0% 31
> Jun 0% 30
>
>