Using date range to monitor invoices received. Help please!

Hi there,

For months now I have been manually tracking the invoices that I have received by putting a "1" in the correct cell and I was wondering if anyone would be kind enough to come up with an automated way.

I have attached an example, I'm looking for the yellow highlighted cells to automatically pick up if an invoice has been received for the correct date period.
Sometimes the invoices cover part of a month or even stretch across 2 months so if possible I would like to pick this up too.

In an absolute IDEAL world, I would love to get the % of the month that the invoice covers, e.g. if an invoice covers the 7 days from 4/4/18 to 9/4/18 that would be 25% of the month so the formula would return 25% in the cell! This may not be possible though!

Thank you so much

Hi Paul, Thanks so much for the quick response, really interesting formula.
It's definitely a step in the right direction but not exactly what I'm after as it involved the towns being in the same order and also won't account for duplicate invoices.

I've added another attachment to try and explain what I mean. I have resorted the towns alongside the left hand side, is there anyway all the yellow cells could also look up to the columns along the left hand side as opposed to relying on the towns in column F being in the same order as those in column A?

I hope I'm making sense!
Thanks again

Can anyone help me

Thank you Paul, that's really great. Is there no way to add another dimension to the formula to tell us how much of the month has been covered by the invoice? e.g. instead of returning "1", returning 25% if 25% of the month is within the date range?

This may not be possible but I thought it would be worth asking.

Thanks so much for your help.

Hi,

I am a novice and probably am wrong but try this formula

=(C5-B5)/(G2-G1)