Show days in a month dynamically + working days

**vemix** · 12-28-2012, 06:33 AM

Hi guys,

Two questions:

1) Is there some simple way to show number of days in a month based on current day of the month ?

What I mean is that I want to have a cell in which I will be having number of days displayed dynamically. For example today it would be 31, in January on any given day it would be 31, on February on any given day it would be 28 etc.,

One way I found is to have a formula like this "=DAY(MONTH(TODAY()))" which displays a value from 1-12 obviously, and then I can do an IFS function that determines cell value (from 28 to 31) based on values from the formula (1 to 12). However, there must be a much simpler way.

2) Is there a way to do the same thing but having number of working days displayed in the month ? or some criteria ? I count that Saturday is a working day.

I'd much appreciate!
vemix

**Fotis1991** · 12-28-2012, 06:41 AM

=day(date(year(a1),month(a1)+1,1)-1)

=networkdays(a1-day(a1)+1,date(year(a1),month(a1)+1,0))

**Andrew-R** · 12-28-2012, 06:43 AM

1) =day(date(year(today()),month(today())+1,1)-1)

2) =sumproduct(--(weekday(row(indirect(date(year(today()),month(today()),1) & ":"& date(year(today()),month(today())+1,1)-1)),2)<>7))

**Fotis1991** · 12-28-2012, 06:47 AM

And without Saturdays

=NETWORKDAYS($A$1-DAY($A$1)+1;DATE(YEAR($A$1);MONTH($A$1)+1;0))+4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)-6))

**Spencer101** · 12-28-2012, 06:47 AM

For the first one I use =DAY(EOMONTH(TODAY(),0))

**daddylonglegs** · 12-28-2012, 07:54 AM

Here's a fun one for total days in the month

=42-DAY(TODAY()-DAY(TODAY())+42)

and for all days except Sundays try

=SUM(INT((WEEKDAY(EOMONTH(TODAY(),-1)-{1,2,3,4,5,6})+DAY(EOMONTH(TODAY(),0))-1)/7))

in both cases format result cell as general

In that latter formula {1,2,3,4,5,6} represents the days you want to include (1 = Monday through to 7 = Sunday) so you can change to any combination, e.g. to count Tuesdays and Thursdays in the month only use {2,4}