# Multiply DATE formatted cells with numbers

#### A Durfani

##### Board Regular
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
ABCD
1DateCustom DaysNumberB*C
22/5/202105203
32/6/2021062.510
42/7/202107304
52/8/2021083.511
62/9/202109406
72/10/2021104.501
82/11/202111508
92/12/2021125.503
102/13/202113609
112/14/2021146.504
122/15/202115711
132/16/2021167.506
Sheet1
Cell Formulas
RangeFormula
B2:B13B2=A2
D2:D13D2=B2*C2
C3:C13C3=C2+0.5

My expected result is

Book2
ABCD
1DateCustom DaysNumberB*C
22/5/20215210
32/6/202162.515
42/7/20217321
52/8/202183.528
62/9/20219436
72/10/2021104.545
82/11/202111555
92/12/2021125.566
102/13/202113678
112/14/2021146.591
122/15/2021157105
132/16/2021167.5120
Sheet2
Cell Formulas
RangeFormula
D2:D13D2=B2*C2

#### Peter_SSs

##### MrExcel MVP, Moderator
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)

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### A Durfani

##### Board Regular
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
ABCDEFGH
1DateText DaysNumberB*CNumeric DaysNumberB*C
23/02/202103260326
327/04/2021272.567.5272.567.5
418/10/20211835418354
523/02/2021233.580.5233.580.5
621/12/20212148421484
710/03/2021104.545104.545
87/10/20210753507535
925/10/2021255.5137.5255.5137.5
103/11/20210361803618
112/02/2021026.513026.513
1221/04/2021217147217147
131/10/2021017.57.5017.57.5
142/04/20210281602816
1513/03/2021138.5110.5138.5110.5
16
17Count if day is 1-1508
Text v Numeric
Cell Formulas
RangeFormula
B2:B15B2=TEXT(A2,"dd")
D2:D15,H2:H15D2=B2*C2
F2:F15F2=DAY(A2)
C3:C15,G3:G15C3=C2+0.5
B17,F17B17=COUNTIF(B2:B15,"<16")
Thank you very much for correcting us.

#### A Durfani

##### Board Regular
It could be Resolved by Adding
"=VALUE(TEXT(A2,"dd"))" or "=NUMBERVALUE(TEXT(A2,"dd"))"
Book1
ABCD
1DateCustom DaysNumberB*C
22/5/20215210
32/6/202162.515
42/7/20217321
52/8/202183.528
62/9/20219436
72/10/2021104.545
82/11/202111555
92/12/2021125.566
102/13/202113678
112/14/2021146.591
122/15/2021157105
132/16/2021167.5120
Sheet2
Cell Formulas
RangeFormula
B2:B13B2=VALUE(TEXT(A2,"dd"))
D2:D13D2=B2*C2
C3:C13C3=C2+0.5

#### ​

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

Book1
ABCDEFGH
1DateText DaysNumberB*CNumeric DaysNumberB*C
22/3/2021326326
34/27/2021272.567.5272.567.5
410/18/20211835418354
52/23/2021233.580.5233.580.5
612/21/20212148421484
73/10/2021104.545104.545
810/7/202175357535
910/25/2021255.5137.5255.5137.5
1011/3/202136183618
112/2/202126.51326.513
124/21/2021217147217147
1310/1/202117.57.517.57.5
144/2/202128162816
153/13/2021138.5110.5138.5110.5
16
17Count if day is 1-1588
Sheet1
Cell Formulas
RangeFormula
B2:B15B2=TEXT(A2,"dd")+0
D2:D15,H2:H15D2=B2*C2
F2:F15F2=DAY(A2)
G3:G15,C3:C15C3=C2+0.5
B17,F17B17=COUNTIF(B2:B15,"<16")

#### Peter_SSs

##### MrExcel MVP, Moderator

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)

#### A Durfani

##### Board Regular
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!

Replies
8
Views
175
Replies
5
Views
117
Replies
9
Views
189
Replies
3
Views
52
Replies
7
Views
132

1,127,620
Messages
5,625,902
Members
416,141
Latest member
Bartek9q

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back