Hi,
Required a formula to help to return the results as mentioned in the sample sheet.
Return the results for a given month and year based on the start and end period dates (in Column C to J)
In the case of related months covering full days, it should return a number of 1; in the case of months not covering a full month, it should return a value like the exact days of a month divided by the full days of that month.
Sample sheet expected results are enclosed.
Many thanks
Try:
=LET(As,EOMONTH($A4,-1)+1,Ae,EOMONTH($A4,0),Be,EOMONTH($B4,-1)+1,Hs,EOMONTH(C$3,0),He,EOMONTH(C$3,0),IF(C$3<As,"",IF(AND(As=C$3,$B4>=Hs),(Hs-$A4+1)/DAY(He),
IF(AND(As=C$3,$B4<Hs),($B4-$A4+1)/DAY(Hs),IF(AND(C$3>Ae,C$3<Be),DAY(Hs)/DAY(Hs),IF(Be=C$3,DAY($B4)/DAY(Hs),""))))))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Many thanks for the formula solution,
i would appreciate it if you could provide a shorter formula, since a large amount of data needs to be applied.
Thanks,
No. I can't.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks