Thanks Richard & zbor for the quick responses I've tweaked my sheet so to represent a clearer explanation of what im trying to achieve..
The sheet represents a payment schedule, & in this case 31 days in the month
If I use examples ii & v it gives me a total of 32 days
In example ii I'm trying to achieve 4 days instead of 5 & in iv 25 instead of 26
The tweaking is in ii & v as explained above
I'v also manually input in col G what im expecting
Hope this ia little clearer
pvd
Hi,
This clearly revolves around whether dates are counted as inclusive or not. But can I check your logic
i) counts the whole month of August - hence start and end dates are inclusive i.e. 31 days
ii) this seems to ignore the requirement to count the 'last' day, i.e. 5th and you want an answer of 4
iii) again this is a complete month as i) above hence 31 days
iv) from 5 Aug to 30 Aug is 26 inclusive days yet you want to ignore one day and show 25
v) from 5 Aug to 31 Aug is 27 inclusive days
What is your rule for ignoring an inclusive day in ii) and iv) but not else where. If it's where the start or end date is within the month then maybe in F3 copied down try
Formula:
=IF(OR(D3>$B$3,E3<$A$3),0,MIN(E3,$B$3)-MAX(D3+1,$A$3)+IF(D3>$A$3,1,0)+IF(E3>$B$3,1,0))
but it doesn't seem entirely consistent
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks