 SOLVED

# Excel - match dates in dfferent formats

Hello,

In the excel attached I am trying to sum all the dates in the second tab into a different format on the first tab.

i.e Sum of all dates in January 2018. Even when I format the dates in second tab to same format as first tab, the formula does not work.

Really appreciate support on this

Many thanks

Jenny

13 Replies

# Re: Excel - match dates in dfferent formats

Format doesn't matter. To sum within month you may use

``=SUMIFS(Sheet2!\$D:\$D,Sheet2!\$C:\$C,">" & EOMONTH(B\$1,-1),Sheet2!\$C:\$C,"<=" & EOMONTH(B\$1,0),Sheet2!\$B:\$B,\$A2)``

# Re: Excel - match dates in dfferent formats

Sorry please see attached. The highlighted cell should bring back 423. The school term is not a date. I am trying to match column B on sheet 2, with column A on sheet 1

Thank you!

# Re: Excel - match dates in dfferent formats

Hi,

Try below formula:

=SUMPRODUCT((YEAR(Sheet2!\$B\$2:\$B\$100000)=YEAR(\$A2))*(MONTH(Sheet2!\$B\$2:\$B\$100000)=MONTH(\$A2))*(Sheet2!\$C\$2:\$C\$100000=B\$1)*(Sheet2!\$D\$2:\$D\$100000))

Hope this helps.

Regards,
IlirU
best response confirmed by JennySommet (Contributor)
Solution

# Re: Excel - match dates in dfferent formats

Sorry for the misprint. Corrected formula is

``=SUMIFS(Sheet2!\$D:\$D, Sheet2!\$B:\$B,">" & EOMONTH(\$A26,-1), Sheet2!\$B:\$B,"<=" & EOMONTH(\$A26,0), Sheet2!\$C:\$C,E\$1)``

# Re: Excel - match dates in dfferent formats

Thank you! This is so useful for me and all my other spreadsheets!

# Re: Excel - match dates in dfferent formats

Thank you! this works!

# Re: Excel - match dates in dfferent formats

@JennySommet , you are welcome, glad it helped

# Re: Excel - match dates in dfferent formats

Hello!
On the same subject how do I get the formula below:

=SUMIFS(Sheet2!\$D:\$D, Sheet2!\$B:\$B,">" & EOMONTH(\$A26,-1), Sheet2!\$B:\$B,"<=" & EOMONTH(\$A26,0), Sheet2!\$C:\$C,E\$1)

To reflect <= Dec 19 rather the formula above which would bring back number of records just for month of Dec 19.

Thank you!

# Re: Excel - match dates in dfferent formats

That could be

``=SUMIFS(Sheet2!\$D:\$D, Sheet2!\$B:\$B,">=" & DATE(2019,12,1), Sheet2!\$B:\$B,"<=" & DATE(2019,12,31), Sheet2!\$C:\$C,E\$1)``

# Re: Excel - match dates in dfferent formats

Thank you! Is this the same as the formula below if cell A6 = Dec 19 then:

\$B:\$B,"<=" & EOMONTH(\$A6,0) would mean less than or equal to Dec 19?

I just want a formula for any month I put in the cell, it doesn't have to be Dec 19. Thank you!

# Re: Excel - match dates in dfferent formats

Yes, practically the same. If in A6 is Dec 15, 2019 when

EOMONTH(A6,-1) returns Nov 30, 2019

EOMONTH(A6,0)  returns Dec 31, 2019

# Re: Excel - match dates in dfferent formats

Thank you, so useful!!

# Re: Excel - match dates in dfferent formats

@JennySommet , glad to help