+ Reply to Thread
Results 1 to 17 of 17

Count the number of days in the year to date less the days in the current month

  1. 08-22-2017, 04:39 AM #1
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Count the number of days in the year to date less the days in the current month

    Hello all,

    In A1 I have Today() In A2 I wish to count the number of days in the year to date less the days in the current month
    I can get the days using =A1-DATE(YEAR(A1),1,0) but I am stuck with the days??

    Thanks
    Kevin
    Reply With QuoteRegister To Reply
  2. 08-22-2017, 05:42 AM #2
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: Count the number of days in the year to date less the days in the current month

    just substract the days in today

    Please Login or Register  to view this content.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you
    Reply With QuoteRegister To Reply
  3. 08-22-2017, 05:53 AM #3
    davsth
    davsth is offline
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,212

    Re: Count the number of days in the year to date less the days in the current month

    =today()-day(today())-date(year(today()),1,1)+1
    Reply With QuoteRegister To Reply
  4. 08-22-2017, 05:55 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Count the number of days in the year to date less the days in the current month

    Surely this is all you need?

    =MONTH(A1)&" months "&DAY(A1)&" days"

    EDIT: Sorry - I misread the requirement!
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  5. 08-22-2017, 05:58 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Count the number of days in the year to date less the days in the current month

    Try this:

    =A1-EOMONTH(A1,-MONTH(A1))+1-DAY(A1)
    Reply With QuoteRegister To Reply
  6. 08-22-2017, 06:15 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Count the number of days in the year to date less the days in the current month

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  7. 08-22-2017, 06:16 AM #7
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,057

    Re: Count the number of days in the year to date less the days in the current month

    Quote Originally Posted by Excelski View Post
    Hello all,

    In A1 I have Today() In A2 I wish to count the number of days in the year to date less the days in the current month
    I can get the days using =A1-DATE(YEAR(A1),1,0) but I am stuck with the days??

    Thanks
    Kevin
    Are you looking for first day of the year until end of last month?

    Then, =EOMONTH(A1,-1)-DATE(YEAR(A1),1,1)
    Reply With QuoteRegister To Reply
  8. 08-22-2017, 07:06 AM #8
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: Count the number of days in the year to date less the days in the current month

    Thanks to everyone, of the 4 solutions they all worked ish 2 where spot on 2 where a day out

    Thanks
    Kevin
    Reply With QuoteRegister To Reply
  9. 08-22-2017, 07:10 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Count the number of days in the year to date less the days in the current month

    For completeness, please tell us which two you consider a day out and why - it will help people reading this thread in future.
    Reply With QuoteRegister To Reply
  10. 08-22-2017, 07:37 AM #10
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: Count the number of days in the year to date less the days in the current month

    Sorry i should have done that

    Keebellah returned 212
    davsth 212
    AliGW 213
    Josephten 211

    Thanks again
    Kevin
    Reply With QuoteRegister To Reply
  11. 08-22-2017, 07:46 AM #11
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Count the number of days in the year to date less the days in the current month

    Just change mine to this, in that case:

    =A1-EOMONTH(A1,-MONTH(A1))-DAY(A1)
    Reply With QuoteRegister To Reply
  12. 08-22-2017, 08:50 AM #12
    shivya
    shivya is offline
    Forum Contributor shivya's Avatar
    Join Date
    08-19-2017
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    240

    Re: Count the number of days in the year to date less the days in the current month

    =sumproduct(--(b38-date(year(b38),1,1)))
    Reply With QuoteRegister To Reply
  13. 08-22-2017, 09:33 AM #13
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: Count the number of days in the year to date less the days in the current month

    AliGW Thank you
    shivya your solution returns 233, not the answer i was looking for, but thanks for looking
    Reply With QuoteRegister To Reply
  14. 08-22-2017, 09:59 AM #14
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,057

    Re: Count the number of days in the year to date less the days in the current month

    Quote Originally Posted by josephteh View Post
    Are you looking for first day of the year until end of last month?

    Then, =EOMONTH(A1,-1)-DATE(YEAR(A1),1,1)
    =EOMONTH(A1,-1)-DATE(YEAR(A1),1,1)+1, if both starting and ending dates are inclusive.
    Reply With QuoteRegister To Reply
  15. 08-22-2017, 10:05 AM #15
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: Count the number of days in the year to date less the days in the current month

    josephteh, thank you
    Reply With QuoteRegister To Reply
  16. 08-22-2017, 10:05 AM #16
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: Count the number of days in the year to date less the days in the current month

    My formula (corrected

    Please Login or Register  to view this content.

    Returns 209
    Reply With QuoteRegister To Reply
  17. 08-22-2017, 11:20 AM #17
    Excelski
    Excelski is offline
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: Count the number of days in the year to date less the days in the current month

    I think your first solution returned the correct result
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. total of days within current month with dates from before the current month and year
    By ozinal in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-29-2016, 01:28 PM
  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. Need to count number of days from a given date in a cell from the current day
    By mhitomi_7 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-27-2015, 06:40 PM
  4. Number of days between two dates but include current date in the count
    By Jimmib78 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-20-2015, 11:59 AM
  5. calculate number of days between two date within a current month including end date
    By ahgoon in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-06-2015, 09:19 PM
  6. How do i write a formula to count the number of days in a date range, split per month
    By benwwatson in forum Excel General
    Replies: 8
    Last Post: 12-04-2013, 04:14 AM
  7. [SOLVED] Count number of days between dates BUT IF null to current date
    By kathi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-14-2006, 12:15 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1