Multiply DATE formatted cells with numbers

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
243
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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)
 

Some videos you may like

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
Joined
Apr 12, 2019
Messages
243
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Apr 12, 2019
Messages
243
Office Version
  1. 2013
Platform
  1. Windows
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
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")
 

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
243
Office Version
  1. 2013
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top