+ Reply to Thread
Results 1 to 15 of 15

display number of days

  1. #1
    Registered User
    Join Date
    10-21-2010
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    34

    display number of days

    Hi,

    I want a formula to calculate and display the number of days completed. below is the data i have

    Column A list of holidays for the month (example for october)

    02-10-2019
    06-10-2019
    07-10-2019
    12-10-2019
    13-10-2019
    20-10-2019
    26-10-2019
    27-10-2019
    28-10-2019

    in Cell B i have total working days 22 (31-9)

    In C1 i want to display the number of days elapsed as on current day.

    any thoughts

    Thanks in advance
    Ram

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: display number of days

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: display number of days

    (Sorry about my English)

    I assume that your target completed date is EOM.
    Please verify if this formula is OK or not.

    =DATE(2019,10,31)-TODAY()-COUNTIFS($A:$A,">="&DATE(2019,10,1),$A:$A,"<="&DATE(2019,10,31))


    ps. you may +1 or -1 to re-correct the answer.

    Regards.

  4. #4
    Registered User
    Join Date
    10-21-2010
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    34

    Re: display number of days

    sample spread sheet attached
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-21-2010
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    34

    Re: display number of days

    The days elapsed should change based on the current day

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: display number of days

    Quote Originally Posted by Ram8516 View Post
    The days elapsed should change based on the current day
    Sorry but that's not helped much.

    Which dates are you using to compare with TODAY() and how for instance do you get 22 in B2.

    What you refer to as Holidays, apart from A2 are all Saturday & Sunday weekend dates so maybe a =NETWORKDAYS() will do.
    But we do need to understand what you define as elapsed periods. So upload a workbook in which you have manually added results you expect and indicate which is original data and which is results.

  7. #7
    Registered User
    Join Date
    10-21-2010
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    34

    Re: display number of days

    Hi Richard,

    1. The holiday list comprises of 2nd and 4th saturday of every month along with other holidays. (example 2nd,7th,28th are non saturday and sunday)
    2. 22 days are arrived by the formula 31(days in october)- 9( list of holidays in october)
    3. I need to calculate the number of days elapsed based on the current date ( example on 4th october the days elapsed would be 2, on 8th October it would be 4 days )

    Hope this helps

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: display number of days

    How do you calculate 4 days on 8th October.

    As I requested you need to upload the workbook with manually added results. In this case add a few columns each representing a differnt day and add rge results you expect to see in each one


    Remember that when you ask a question in a forum those of us who might be able to help know absolutely nothing about your system and work process or the terminology you use. Unless you have uploaded a workbook we know nothing about how it is laid out and little about what you want done with it or how the results should be presented.

    Whilst these things are second nature to you since you live with the workbook all the working day we start with zero knowledge so you need to take us by the hand and explain in quite precise detail your aims and goals as well as stuff like how your data might look different in other situations.
    Don't assume we are able to "figure it out". We're usually quite good but not prescient.

    Put yourself in our position and think about whether what you've posted makes sense and is unambiguous to someone who's never seen it before.
    You are asking us for help so help us to be able to help you by providing all the information we need, even if that information seems obvious or intuitive to you.

  9. #9
    Registered User
    Join Date
    10-21-2010
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    34

    Re: display number of days

    Hi Richard,

    I have updated the spread sheet and have uploaded it again with this thread. column C is the example of current date and I need to populate only E2 (for the month of October) . so if today's date is 3rd October then value in E2 should be 2, it today's date is 15th October then E2 should be 10.

    The holiday lists is manual entry and not necessarily only Saturday and Sunday.

    Hope this sheet is better than the original

    Thank you
    Ram
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: display number of days

    Please try this formula.

    E2
    =C2-DATE(YEAR(C2),MONTH(C2),1)+1-COUNTIFS(A$2:A$58,">="&DATE(YEAR(C2),MONTH(C2),1),A$2:A$58,"<="&C2)


    ** C2 is refered to current date.

    Regards.

  11. #11
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: display number of days

    ...another variation

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: display number of days

    Or try:

    =NETWORKDAYS.INTL(C2,TODAY(),"0000000",$A$2:$A$58)

  13. #13
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: display number of days

    Quote Originally Posted by Phuocam View Post
    Or try:

    =NETWORKDAYS.INTL(C2,TODAY(),"0000000",$A$2:$A$58)
    The obvious is even better...

  14. #14
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: display number of days

    Quote Originally Posted by Richard Buttrey View Post
    The obvious is even better...
    Thanks for the rep point.

  15. #15
    Registered User
    Join Date
    10-21-2010
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    34

    Re: display number of days

    Quote Originally Posted by menem View Post
    Please try this formula.

    E2
    =C2-DATE(YEAR(C2),MONTH(C2),1)+1-COUNTIFS(A$2:A$58,">="&DATE(YEAR(C2),MONTH(C2),1),A$2:A$58,"<="&C2)


    ** C2 is refered to current date.

    Regards.
    This works fine. Thank you . Special thank you to Richard for making me explain the problem in detail. Thank yuu

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to display the last days days in pivot table?
    By sovietchild in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 04-13-2018, 08:42 AM
  2. [SOLVED] calculate total number of days between two days based on 365 days year
    By aparunkumar in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-14-2016, 12:51 AM
  3. Replies: 2
    Last Post: 04-16-2013, 08:14 AM
  4. [SOLVED] display number of days if less than 7 otherwise round up to months
    By chrisio in forum Excel General
    Replies: 3
    Last Post: 09-20-2012, 08:35 AM
  5. Display number of days in a set range
    By ChrisDubau in forum Excel General
    Replies: 5
    Last Post: 10-15-2009, 10:07 PM
  6. display a number as weeks and days?
    By di22y in forum Excel General
    Replies: 3
    Last Post: 01-30-2009, 07:22 PM
  7. Replies: 6
    Last Post: 03-20-2005, 02:06 PM

Tags for this Thread

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