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:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi,

Not sure to understand your constraints ...

Have you tried : =C2-B2
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
You can thank Marcelo ... who has guessed you were looking for OVERLAP ... :cool:
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top