Date format dd/mm/yyyy hh:mm to Date

[AliciaGoyanes]

Hi everyone,
I am trying to link through an =match formula some dates. However, the data I get (as it is linked to bloomberg), is downloaded in format dd/mm/yyyy hh:mm.
What I want though is to sum the values of one of the columns by day format 24/10/2022.
However, I am struggling to match the dates.

Let me know if not clear.

Thank you

 

[alansidman]

Please upload sample data(6-8 records) using XL2BB. Then show us a mock up of what you expect the results to be for those records. Do not load pictures as we cannot manipulate data in a picture

 

[AliciaGoyanes]

Let me know if clear as I show below please, I cannot make xl2bb work in my mac somehow: sorry.

A1		B1	C1		D1
25/10/2022​		25/10/2022 10:00​	10​		=sum(C:C and match (B:B) with A:A)
24/10/2022​		25/10/2022 11:00​	15​
23/10/2022​		25/10/2022 19:00​	20​
22/10/2022​		25/10/2022 18:00​	10​
		24/10/2022 10:00​	12​
		24/10/2022 11:00​	17​
		24/10/2022 19:00​	19​
		24/10/2022 18:00​	20​

 

[AliciaGoyanes]

Is it clear as I showed or should I put it differently?
Sorry I am new here.

 

[Dave Patton]

T202210a.xlsm
	A	B	C	D	E	F
1	25-Oct-22	25-Oct-22 10:00	10		55	55
2	24-Oct-22	25-Oct-22 11:00	15		68	68
3	23-Oct-22	25-Oct-22 19:00	20
4	22-Oct-22	25-Oct-22 18:00	10
5		24-Oct-22 10:00	12
6		24-Oct-22 11:00	17
7		24-Oct-22 19:00	19
8		24-Oct-22 18:00	20
4d

Cell Formulas
Range	Formula
E1:E2	E1	=SUMPRODUCT(--(INT($B$1:$B$8)=A1),$C$1:$C$8)
F1:F2	F1	=SUMIFS(C:C,B:B,">="&A1,B:B,"<"&A1+1)

 

[Dave Patton]

If the problem is with the data import, convert the dates to real dates with Data Text to Columns criteria dmy and do not include the time part.

Also ensure that the numbers are recognized as real numbers.

T202210a.xlsm
	A	B	C	D	E	F
19	October 25, 2022		October 25, 2022	10		55
20	October 24, 2022		October 25, 2022	15		68
21	October 23, 2022		October 25, 2022	20
22	October 22, 2022		October 25, 2022	10
23			October 24, 2022	12
24			October 24, 2022	17
25			October 24, 2022	19
26			October 24, 2022	20
27
4d

Cell Formulas
Range	Formula
F19:F20	F19	=SUMIFS($D$19:$D$26,$C$19:$C$26,A19)

 

[AliciaGoyanes]

Hi Dave, thank you so much for your help.
One question please, if I wanted to make your formula scalable so that in one formula I include a range of dates. Let's say in your E1 formula, I want to include not only the 25/10/2022 but also the 24/10/2022. Plan is to include various.

Is there any way to do so?

 

[Dave Patton]

T202210a.xlsm
	B	C	D	E
1		Criteria		25-Oct-22
2				24-Oct-22
3
4	25-Oct-22 10:00	10		123
5	25-Oct-22 11:00	15
6	25-Oct-22 19:00	20
7	25-Oct-22 18:00	10
8	24-Oct-22 10:00	12
9	24-Oct-22 11:00	17
10	24-Oct-22 19:00	19
11	24-Oct-22 19:00	20
12	23-Oct-22 19:00	100
4d

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(--(INT($B$4:$B$11)>=$E$2),--(INT($B$4:$B$11)<=$E$1),$C$4:$C$11)
B12	B12	=B11-1