SOLVED

Excel - match dates in dfferent formats

%3CLINGO-SUB%20id%3D%22lingo-sub-2198912%22%20slang%3D%22en-US%22%3EExcel%20-%20match%20dates%20in%20dfferent%20formats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2198912%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20excel%20attached%20I%20am%20trying%20to%20sum%20all%20the%20dates%20in%20the%20second%20tab%20into%20a%20different%20format%20on%20the%20first%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei.e%20Sum%20of%20all%20dates%20in%20January%202018.%20Even%20when%20I%20format%20the%20dates%20in%20second%20tab%20to%20same%20format%20as%20first%20tab%2C%20the%20formula%20does%20not%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%20appreciate%20support%20on%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJenny%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2198912%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2199043%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20match%20dates%20in%20dfferent%20formats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2199043%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20doesn't%20matter.%20To%20sum%20within%20month%20you%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIFS(Sheet2!%24D%3A%24D%2CSheet2!%24C%3A%24C%2C%22%26gt%3B%22%20%26amp%3B%20EOMONTH(B%241%2C-1)%2CSheet2!%24C%3A%24C%2C%22%26lt%3B%3D%22%20%26amp%3B%20EOMONTH(B%241%2C0)%2CSheet2!%24B%3A%24B%2C%24A2)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Contributor

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

@JennySommet 

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)

@Sergei Baklan 

 

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!

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

@JennySommet 

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)
Thank you! This is so useful for me and all my other spreadsheets!
Thank you! this works!

@JennySommet , you are welcome, glad it helped

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!

@JennySommet 

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)
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!

@JennySommet 

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

 

Thank you, so useful!!

@JennySommet , glad to help