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

1. ## 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

2. ## 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!

3. ## 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. ## 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. ## Re: Need formula to calculate days used in month initiated in previous and selected month

Maybe my solution with formula help you.

Ardamit

#### Thread Information

##### Users Browsing this Thread

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

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