Search code examples
exceldatesumifs

SUMIF formula to use a different format of data (UK format)

I currently use EXCEL in US data format, however I work with some folk over the pond in UK. I am trying to do some work on their spreadsheet however I'm running into an issue.

This is what the data looks like.

First image is my result set.

enter image description here

enter image description here

So this is the set up for when I have US format Dates. The fields in yellow have the following formula:

=SUMIFS($C$8:$C$11,$A$8:$A$11,$A4,$B$8:$B$11,"<6/30/2020")

What I'm trying to do is convert the DATE column so it's in UK format.

Image below shows how I changed the Date to UK:

enter image description here

The thing is though, those dates in DATE column are in UK format and I'm trying to use the same formula to achieve the same results as they are in the first image.

Regardless of this, if I now try to change the formula in those same cells to:

=SUMIFS($C$8:$C$11,$A$8:$A$11,$A4,$B$8:$B$11,"<30/06/2020") <--- it does not pick up those same hours.

I've tried to convert it using an Excel built in function TEXT to no avail. Here is what I tried to do:

=SUMIFS($C$8:$C$11,$A$8:$A$11,$A4,$B$8:$B$11,< TEXT("30/06/2020","dd/mm/yyyy"))

THis does not work at all. I seem to have a problem with the LESS than sign < that is before the date.

Does anyone have any idea if something like this is doable? Having dates be formatted in UK, and then using the SUMIFS formula to still be able to capture the Hours?

Thanks.

Solution

  • Use DATE():

    =SUMIFS($C$8:$C$11,$A$8:$A$11,$A4,$B$8:$B$11,"<"&DATE(2020,6,30))