Multiply DATE formatted cells with numbers

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 · Feb 5, 2021

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

Sufiyan97 · Feb 5, 2021

Dossfm0q said:
Try This

Book1
A B C D
1 Date Custom Days Number B*C
2 2/5/2021 05 2 10
3 2/6/2021 06 2.5 15
4 2/7/2021 07 3 21
5 2/8/2021 08 3.5 28
6 2/9/2021 09 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
Sheet1
Cell Formulas
Range Formula
B2:B13 B2 =TEXT(A2,"dd")
D2:D13 D2 =B2*C2
C3:C13 C3 =C2+0.5

Thank you very much!
Works perfect.

Sufiyan97 · Feb 5, 2021

Peter_SSs said:
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 · Feb 5, 2021

alidurfani said:
Thank you very much Peter,

You are welcome.

alidurfani said:
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.

Sufiyan97 · Feb 5, 2021

Peter_SSs said:
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 · Feb 6, 2021

alidurfani said:
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