Help with Date Formulas with a twist (# of Days between Start & End date)

[Josiev]

Hello,

I am struggling to create a formula that calculates total number of days for each month based on a given Start date and End date. I have been counting the days manually using a calendar and this has become very tedious given the # of incidents that we need to track. I am hoping there is a formula out there that can automate this tracking report.

Attached is a sample worksheet. The orange cells are where a formula is needed. It needs to calculate # of days lost taking into account the first day lost. The good news is we need to include each day of the month so no need to exclude weekends or holidays.

Any assistance is greatly appreciated.

 

[NARAYANK991]

Hi ,

See your file now.

Narayan

 

[Josiev]

Wow...this is great! I was fooling around with date formulas but did not succeed. Your formulas look easy enough to understand but I wouldn't have guessed using MAX & MIN. Thank you so much!

 

[Chief Ace]

Hi Narayan,

I know the original post was back in 2014. I spent all day today trying to figure out this formula below, but it was not working across the new year; it just gave me zero results.

=IF(OR(MONTH($A2)>MONTH(D$1),MONTH($B2)<MONTH(D$1)),0,MIN($B2,DATE(YEAR(D$1),MONTH(D$1)+1,0))-MAX($A2,D$1))

However, when I visited this forum and did some research, I found your answer to be exactly what I needed. I mimicked the original poster's spreadsheet, and the formula you provided was spot on. It's exactly what I was looking for. Thank you for saving me some headache.

Cheers

Chief Ace