Time Intelligence Calculation

[MSKarthikeyan]

In my data, I have From Date and To Date and the number of days between them, as well as another field called Reporting Date. My requirement is, for example, a person working in a contract from 10-04-2024 to 20-10-2024, but the company provided a bill by the following month, which is 01-05-2024. So far, I have not paid for the month of April, and I will pay for April and May put together in May month because I received the bill only on May 1, 2024, and from next month onwards till the end of the contract, I will pay monthly. Like this, there are certain scenarios that I have highlighted in the Excel. Please help me out with the formula accordingly. Thank you so much

[josephteh]

Try,
F5=DATE(LEFT($C$2,4),4,30)
G5=EOMONTH(F5,1), copy across.
F6=(MAX(0,MIN($C6,F$5)-MAX($B6,$E6,EOMONTH(F$5,-1)+1)+1)+MAX(0,IF(EOMONTH($E6,0)=F$5,$E6-$B6,0)))/$D6*$A6, copy across and down.

[MSKarthikeyan]

I am expressing my sincere gratitude for your wonderful assistance, which arrived on time. Your recipe has made me smile.

[MSKarthikeyan]

Hi Josephteh,

I am expressing my sincere gratitude for your wonderful assistance, which arrived on time. Your recipe has made me smile.

[josephteh]

You are welcome! Thanks for your feedback and Rep!

[MSKarthikeyan]

Hi Josephteh,

The last time, you helped me with the fantastic formula, however, there's a tiny bug in it that prevents it from working in some situations, Would you kindly assist me with this?
If the start date and End date are with the previous FY and the report date is with the Current FY, in that scenario, I am facing error
If the start date is the previous FY and the end date and report date are the current FY, I am facing an error in that scenario.

I have highlighted the error in red colour.

Please help me out. Thank you so much

[josephteh]

I don't understand what you are saying. Can you manually put in your expected results please?

[josephteh]

Also, please confirm if reporting date is 01/05/24, is the reporting period from 01/05/24 to 30/04/25?

[josephteh]

Try, =IF($C6<$E6,0,(MAX(0,MIN($C6,F$5)-MAX($B6,$E6,EOMONTH(F$5,-1)+1)+1)+MAX(0,IF(EOMONTH($E6,0)=F$5,$E6-$B6,0))))/$D6*$A6