+ Reply to Thread
Results 1 to 5 of 5

Need formula to calculate days used in month initiated in previous and selected month

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    Need formula to calculate days used in month initiated in previous and selected month

    Apologies if this has been covered already, but could not find anything when browsed.

    Attachment cols B - C list eight hiring periods, some of which span more than one month.

    Need a formula covering the whole range that combines four criteria to calculate the number of Hire Days in a month (March in the example):

    Hire Days ending in the month, where the hire started in a previous month
    Hire Days ending in the month, where the hire started that month
    Hire Days running beyond that month, where the hire started in a previous month
    Hire Days running beyond that month, where the hire started in that month

    Cols F - K show the total should be 107 Hire Days for March.

    Hope someone can see the wood for the trees?

    Ochimus
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Need formula to calculate days used in month initiated in previous and selected month

    I use nested IF & AND functions in the attached file.
    For some reason, I get access denied message by Sucuri Firewall when I try to post the formula here.

    Good luck!
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    Re: Need formula to calculate days used in month initiated in previous and selected month

    Estevaoba,

    Apologies for delayed thanks (RL go in the way!), appreciate your prompt response, and share the frustration about the Securi Firewall, which has blocked me on numerous posts!

    Your formula works perfectly on a "record by record" basis, but proves the answer will have to be a Macro.

    The formula approach would "add" the totals for each relevant record to produce the single number in C13. But the "real" file will have over one year's activity, and that's impractical because Excel is capped at 32,767 characters in each cell, but can display only the first 1,024 characters in each cell.

    At least I can mark this as "solved" and thank you with a Reputation point!

    Ochimus

  4. #4
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Need formula to calculate days used in month initiated in previous and selected month

    Well, thank you for your feedback and the rep point.
    Should you not find a vba solution, maybe you could try and split period in months across helper columns or something.

    Take care!

  5. #5
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: Need formula to calculate days used in month initiated in previous and selected month

    Maybe my solution with formula help you.

    Ardamit
    Attached Files Attached Files

+ 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. Formula To Calculate Month in a Previous Date
    By breader21 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2020, 10:44 PM
  2. [SOLVED] Formula to calculate working days and calendar days per month-year ?
    By donny007 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-05-2018, 06:23 PM
  3. Replies: 2
    Last Post: 06-01-2018, 03:33 AM
  4. [SOLVED] calculate the number of days in a month through 'Month Name'
    By pipsmultan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-30-2017, 03:10 PM
  5. [SOLVED] Dynamic formula to calculate the difference between current and previous month
    By kay007 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2015, 11:19 AM
  6. Replies: 5
    Last Post: 10-04-2012, 07:06 AM
  7. Replies: 4
    Last Post: 08-26-2010, 07:32 AM

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