Sumifs using two date ranges

Abvlecxe

Board Regular
Joined
Sep 10, 2015
Messages
53
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
 
Last edited:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

Not sure to understand your constraints ...

Have you tried : =C2-B2
 

Abvlecxe

Board Regular
Joined
Sep 10, 2015
Messages
53
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.
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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.
 
Last edited:

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
You can thank Marcelo ... who has guessed you were looking for OVERLAP ... :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,518
Messages
5,523,352
Members
409,512
Latest member
Exceldoktor

This Week's Hot Topics

Top