Multiply DATE formatted cells with numbers

[Sufiyan97]

I ham having below problem as I have formatted column B as Custom "dd" and when I multiply with numbers in column C it is not giving perfect result

Book2
	A	B	C	D
1	Date	Custom Days	Number	B*C
2	2/5/2021	05	2	03
3	2/6/2021	06	2.5	10
4	2/7/2021	07	3	04
5	2/8/2021	08	3.5	11
6	2/9/2021	09	4	06
7	2/10/2021	10	4.5	01
8	2/11/2021	11	5	08
9	2/12/2021	12	5.5	03
10	2/13/2021	13	6	09
11	2/14/2021	14	6.5	04
12	2/15/2021	15	7	11
13	2/16/2021	16	7.5	06
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=A2
D2:D13	D2	=B2*C2
C3:C13	C3	=C2+0.5

My expected result is

Book2
	A	B	C	D
1	Date	Custom Days	Number	B*C
2	2/5/2021	5	2	10
3	2/6/2021	6	2.5	15
4	2/7/2021	7	3	21
5	2/8/2021	8	3.5	28
6	2/9/2021	9	4	36
7	2/10/2021	10	4.5	45
8	2/11/2021	11	5	55
9	2/12/2021	12	5.5	66
10	2/13/2021	13	6	78
11	2/14/2021	14	6.5	91
12	2/15/2021	15	7	105
13	2/16/2021	16	7.5	120
Sheet2

Cell Formulas
Range	Formula
D2:D13	D2	=B2*C2

 

[Peter_SSs]

It could be Resolved by Adding
"=VALUE(TEXT(A2,"dd"))" or "=NUMBERVALUE(TEXT(A2,"dd"))"

It could be, but why look for a complicated approach when there is an extremely simple one available? =DAY(A2)

 

[Sufiyan97]

It depends what calculations that you are trying to do but yes, it could give you errors.

Here is an example. Column B uses the Text value for 'day' and column F uses the numerical value
You can see that doing the multiplications that you were doing in your example above, there is no difference between column D and column H.
However, if you wanted to use the 'Days' column to count how many of the dates occurred before the 16th of the month, cell F17 gives the correct result but B17 is not correct.

21 02 05.xlsm
	A	B	C	D	E	F	G	H
1	Date	Text Days	Number	B*C		Numeric Days	Number	B*C
2	3/02/2021	03	2	6		03	2	6
3	27/04/2021	27	2.5	67.5		27	2.5	67.5
4	18/10/2021	18	3	54		18	3	54
5	23/02/2021	23	3.5	80.5		23	3.5	80.5
6	21/12/2021	21	4	84		21	4	84
7	10/03/2021	10	4.5	45		10	4.5	45
8	7/10/2021	07	5	35		07	5	35
9	25/10/2021	25	5.5	137.5		25	5.5	137.5
10	3/11/2021	03	6	18		03	6	18
11	2/02/2021	02	6.5	13		02	6.5	13
12	21/04/2021	21	7	147		21	7	147
13	1/10/2021	01	7.5	7.5		01	7.5	7.5
14	2/04/2021	02	8	16		02	8	16
15	13/03/2021	13	8.5	110.5		13	8.5	110.5
16
17	Count if day is 1-15	0				8
Text v Numeric

Cell Formulas
Range	Formula
B2:B15	B2	=TEXT(A2,"dd")
D2:D15,H2:H15	D2	=B2*C2
F2:F15	F2	=DAY(A2)
C3:C15,G3:G15	C3	=C2+0.5
B17,F17	B17	=COUNTIF(B2:B15,"<16")

Thank you very much for correcting us.

 

[Sufiyan97]

Thank you Peter_SSs For your Comments and your Advice
It could be Resolved by Adding
"=VALUE(TEXT(A2,"dd"))" or "=NUMBERVALUE(TEXT(A2,"dd"))"

Book1
	A	B	C	D
1	Date	Custom Days	Number	B*C
2	2/5/2021	5	2	10
3	2/6/2021	6	2.5	15
4	2/7/2021	7	3	21
5	2/8/2021	8	3.5	28
6	2/9/2021	9	4	36
7	2/10/2021	10	4.5	45
8	2/11/2021	11	5	55
9	2/12/2021	12	5.5	66
10	2/13/2021	13	6	78
11	2/14/2021	14	6.5	91
12	2/15/2021	15	7	105
13	2/16/2021	16	7.5	120
Sheet2

Cell Formulas
Range	Formula
B2:B13	B2	=VALUE(TEXT(A2,"dd"))
D2:D13	D2	=B2*C2
C3:C13	C3	=C2+0.5

​

Or simply add +0 to convert text number into actual number

Book1
	A	B	C	D	E	F	G	H
1	Date	Text Days	Number	B*C		Numeric Days	Number	B*C
2	2/3/2021	3	2	6		3	2	6
3	4/27/2021	27	2.5	67.5		27	2.5	67.5
4	10/18/2021	18	3	54		18	3	54
5	2/23/2021	23	3.5	80.5		23	3.5	80.5
6	12/21/2021	21	4	84		21	4	84
7	3/10/2021	10	4.5	45		10	4.5	45
8	10/7/2021	7	5	35		7	5	35
9	10/25/2021	25	5.5	137.5		25	5.5	137.5
10	11/3/2021	3	6	18		3	6	18
11	2/2/2021	2	6.5	13		2	6.5	13
12	4/21/2021	21	7	147		21	7	147
13	10/1/2021	1	7.5	7.5		1	7.5	7.5
14	4/2/2021	2	8	16		2	8	16
15	3/13/2021	13	8.5	110.5		13	8.5	110.5
16
17	Count if day is 1-15	8				8
Sheet1

Cell Formulas
Range	Formula
B2:B15	B2	=TEXT(A2,"dd")+0
D2:D15,H2:H15	D2	=B2*C2
F2:F15	F2	=DAY(A2)
G3:G15,C3:C15	C3	=C2+0.5
B17,F17	B17	=COUNTIF(B2:B15,"<16")

 

[Sufiyan97]

Try this in "D2"...
"=MONTH(B2)*C2"

Thanks for the reply but it is not working for me

Book1
	A	B	C	D
1	Date	Custom Days	Number	B*C
2	2/5/2021	05	2	04
3	2/6/2021	06	2.5	5
4	2/7/2021	07	3	6
5	2/8/2021	08	3.5	7
6	2/9/2021	09	4	8
7	2/10/2021	10	4.5	9
8	2/11/2021	11	5	10
9	2/12/2021	12	5.5	11
10	2/13/2021	13	6	12
11	2/14/2021	14	6.5	13
12	2/15/2021	15	7	14
13	2/16/2021	16	7.5	15
Sheet2

Cell Formulas
Range	Formula
B2:B13	B2	=A2
D2:D13	D2	=MONTH(B2)*C2
C3:C13	C3	=C2+0.5

 

[Peter_SSs]

Or simply add +0 to convert text number into actual number

That is yet another way but my earlier question still stands

why look for a complicated approach when there is an extremely simple one available? =DAY(A2)

 

[Sufiyan97]

That is yet another way but my earlier question still stands

Yes, its better to use simple approach to understand easily.

Thank you very much again!

 

[Peter_SSs]