Sumifs using two date ranges

[Abvlecxe]

Hi,

I am struggling to find a formula to sum utilisation days using two date ranges which is a dump from a system, ideally i would get users to enter info one day at a time but that's not possible and they group info as per example below:

Employee........Date From.......Date To .............Utilisation
Employee A ......17/06/2019 ......20/06/2019..............4

The info / dates i need range i need to compare the above to is

Name: Employee A
Start: 18/06/2019
End: 17/06/2020

A normal sumifs between the two dates along the lines of the below doesn't work for the line above:

sumifs(utilisation,Employee,Name,Date From,">="&start,Date To,"<="&End)

The above formula does work where the Date From and Date To only represent 1 day so Date From: 17/06/2019, Date To: 17/06/2019, Utilisation 1.

Does anyone know a formula which will give me the answer that i am looking for which is 3, which relates to 18,19,20th?

Thanks

 

[James006]

Hi,

Not sure to understand your constraints ...

Have you tried : =C2-B2

 

[Abvlecxe]

Hi,

Not sure to understand your constraints ...

Have you tried : =C2-B2

Hi a system output gives me lines of transactions in the format of the first section "Employee Name", "Date From", "Date To" & "Utilisation", on another tab i am trying to sum the utilisation days for each employee dependent on the date period the user wants to look at which is the "start" & "End Date" but because the ranges cross eachother sumifs doesn't workl as it needs both dates to be equal to or less than the start and end dates.

 

[Marcelo Branco]

To calculate the overlap days between two ranges of dates you should use a formula like this
=MAX(0,MIN(DateTo, End) - MAX(DateFrom, Start) +1)

M.

 

[James006]

You can thank Marcelo ... who has guessed you were looking for OVERLAP ...