MS Excel...

[Dall]

Brains gone to sleep...

Am looking for two commands:

1) Have cell display how many days in the current month.

2) Have cell display which date of the month it is is 16 for the 16...

Basically I have a forecasting SS which works out what we've done to date and where we should be at current run rate at the end of it, rather than manually enter these two figures everyday/month I would like to be lazy and get them to update everytime I load the sheet...

Any clues ?

[idaBigA]

Not very elegant, but... No. of days in the current month

=INT(DATE(YEAR(NOW()),MONTH(NOW())+1,DAY(NOW()))-NOW())

and.. Current Day

=DAY(NOW())

[Dall]

=DAY works spot on the int statement returns 30 days when there are 31 days in october but thats exactly what I am after

Any quick fixes to get an accurate return for the int statement ?

[Incognito]

Not very elegant, but... No. of days in the current month

=INT(DATE(YEAR(NOW()),MONTH(NOW())+1,DAY(NOW()))-NOW())

and.. Current Day

=DAY(NOW())

perfect!!! but note: cell format must be in GENERAL (Category).

[Dall]

perfect!!! but note: cell format must be in GENERAL (Category).

Not very elegant, but... No. of days in the current month

=INT(DATE(YEAR(NOW()),MONTH(NOW())+1,DAY(NOW()))-NOW())

and.. Current Day

=DAY(NOW())

Slight ammendment needed but think it works will know next month:

=INT(DATE(YEAR(NOW()),MONTH(NOW())+1,DAY(NOW())+1)-NOW())

[idaBigA]

Sorry about that, should have checked it a little more.. instead of using INT, you could use ROUNDUP

And to check next months use this...

=ROUNDUP(DATE(YEAR(NOW()),MONTH(NOW())+1,DAY(NOW() ))-DATE(YEAR(NOW()),MONTH(NOW())+0,DAY(NOW())),0)

And the month after ETC...

=ROUNDUP(DATE(YEAR(NOW()),MONTH(NOW())+2,DAY(NOW() ))-DATE(YEAR(NOW()),MONTH(NOW())+1,DAY(NOW())),0)
=ROUNDUP(DATE(YEAR(NOW()),MONTH(NOW())+3,DAY(NOW() ))-DATE(YEAR(NOW()),MONTH(NOW())+2,DAY(NOW())),0)
=ROUNDUP(DATE(YEAR(NOW()),MONTH(NOW())+4,DAY(NOW() ))-DATE(YEAR(NOW()),MONTH(NOW())+3,DAY(NOW())),0)

[Dall]

Is there a way to specify a month so I can have one spreadsheet that keeps uptodate throughout the year on different workbooks, I've tried playing with the:

MONTH(NOW()) changing now to 10, oct, october etc.

It's amazing how an idea can grow so quickly lol

[idaBigA]

I don't quite follow you there, NOW() can be changed to any date, but it has to be a date.

=ROUNDUP(DATE(YEAR(C8),MONTH(C8)+1,DAY(C8))-DATE(YEAR(C8),MONTH(C8)+0,DAY(C8)),0)

In that case, if you put any date into the Cell C8, it will tell you how many days in that month.

i.e. in C8, if you put 20/10/08 it will return 31, if you put 17/02/08 it will return 29