# 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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Peter_SSs

##### MrExcel MVP, Moderator
formatted column B as Custom "dd"
Changing the format of a date like that does not change the underlying value so column B actually contains exactly the same values as column A

Perhaps you want this formula in column B (formatted as General or as Number)

=DAY(A2)

#### EXCEL MAX

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

Last edited:

#### A Durfani

##### Board Regular
Changing the format of a date like that does not change the underlying value so column B actually contains exactly the same values as column A

Perhaps you want this formula in column B (formatted as General or as Number)

=DAY(A2)
Thank you very much Peter,

Now I understand the formatting concept.

and there are no option to mark 2 answer as solution so I have marked Dossfm0q answer based on earlier reply.

#### Peter_SSs

##### MrExcel MVP, Moderator

Thank you very much Peter,
You are welcome.

and there are no option to mark 2 answer as solution so I have marked @Dossfm0q answer based on earlier reply.
I do wonder about converting column B to a text value though when you then immediately force it back to a numerical value with the multiplication. Seems more sensible to me to me to stick to numerical values for arithmetical calculations (& the numerical value could be formatted to show any leading zeros if that is important).

However, you are free to use whichever whichever one you want.

#### A Durfani

##### Board Regular
You are welcome.

I do wonder about converting column B to a text value though when you then immediately force it back to a numerical value with the multiplication. Seems more sensible to me to me to stick to numerical values for arithmetical calculations (& the numerical value could be formatted to show any leading zeros if that is important).

However, you are free to use whichever whichever one you want.
Will it give any error when we use text formula for numeric values?

#### Peter_SSs

##### MrExcel MVP, Moderator
Will it give any error when we use text formula for numeric values?
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")

#### Dossfm0q

##### Banned User
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

Replies
8
Views
161
Replies
5
Views
112
Replies
9
Views
175
Replies
3
Views
50
Replies
7
Views
124

1,127,082
Messages
5,622,594
Members
415,909
Latest member
vbaBeginner94

### 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.

### Which adblocker are you using?

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

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