countdown dates by month

S

shopaholic

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
 
A

Arvi Laanemets

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
 
Top