How to apply a formula when dates fall into a specific range

I have three cells in excel, two input cells with start date and end date and output cells. If the start date and end date falls between 1/1/23 and 2/28/23 then it should count the number of days between start date and end date and multiply it by 100.

• Scenario 1: if the start date is 1/2/23 and end date is 1/31/23 (which falls within the date range), then it should count the number of days 1/2/23 to 1/31/23 and multiply by 100, i.e. expected output is 30X100=3000

• Scenario 2: if the start date is 1/2/23 and end date is 5/31/23 then it should count the number of days between 1/2/23 and 2/28/23 and multiply by 100. For remaining days (3/1/23 to 5/31/23) it shouldn’t apply the formula. i.e. expected output is 58X100=5800

  MEDevel.com: Open-source for Healthcare and Education

  Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

  Visit Medevel

• Scenario 3: if the start date is 12/1/22 and end date is 1/2/23 then it should count the number of days between 1/1/23 and 1/2/23 and multiply by 100. For remaining days (12/1/22 to 12/31/22) it shouldn’t apply the formula. i.e. expected output is 2X100=200

I have tried:

=IF(OR(D10>"1/1/2023",(E10<="2/28/23")),100*DAYS(E10,D10),0)

Where D10 is the start date and E10 is the end date. This formula works okay for scenario 1, but doesn’t work for other scenarios.

Is there a way to do this in excel?

>Solution :

Use min and max:

=MAX((MIN(DATE(2023,2,28),E10)-MAX(DATE(2023,1,1),D10)+1),0)*100