Multiply DATE formatted cells with numbers

A Durfani

Active Member
Joined
Apr 12, 2019
Messages
256
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Try this in "D2"...
"=MONTH(B2)*C2"
 
Last edited:

A Durfani

Active Member
Joined
Apr 12, 2019
Messages
256
Office Version
  1. 2013
Platform
  1. Windows

A Durfani

Active Member
Joined
Apr 12, 2019
Messages
256
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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. (y)
 

A Durfani

Active Member
Joined
Apr 12, 2019
Messages
256
Office Version
  1. 2013
Platform
  1. Windows
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. (y)
Will it give any error when we use text formula for numeric values?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows
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")
 
Solution

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
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

 

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,040
Members
416,955
Latest member
Gohar hussain

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