+ Reply to Thread
Results 1 to 6 of 6

Show days in a month dynamically + working days

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    Berlin, Germany
    MS-Off Ver
    Excel 2007
    Posts
    57

    Show days in a month dynamically + working days

    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

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Show days in a month dynamically + working days

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

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

    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Show days in a month dynamically + working days

    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))

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Show days in a month dynamically + working days

    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))

  5. #5
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: Show days in a month dynamically + working days

    For the first one I use =DAY(EOMONTH(TODAY(),0))
    If I've been of help, please hit the star

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Show days in a month dynamically + working days

    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}
    Last edited by daddylonglegs; 12-28-2012 at 07:56 AM.
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1