SOLVED

New Contributor

# Multiply Hours and Minutes by a number

I have setup a spreadsheet to monitor my staff's holiday hours. I have formatted the cells using [h]:mm and also used the 1904 date system.

My problem is that, when trying to multiply the hours by the number of days taken, e.g. 10hrs 46mins by 2 I get a Value message in the cell. I formula I use is (A3 equals 10:46) =A3*2, can anyone advise me of the correct formula to use? Any assistance would be gratefully received.

8 Replies

# Betreff: Multiply Hours and Minutes by a number

Please read this information. It is of great help to you and everyone who would like to help.

https://techcommunity.microsoft.com/t5/excel/welcome-to-your-excel-discussion-space/m-p/2204395

Then, if I may recommend you, add a file (without sensitive data) and explain exactly what your plan is.

Thank you for your understanding and patience.

Nikolino

# Re: Multiply Hours and Minutes by a number

In general it works

Perhaps you have text somewhere. Better if you could provide simple sample file to check.

# Re: Multiply Hours and Minutes by a number

I am using an Acer Aspire 5 laptop running Windows 10 version 20H2, Microsoft Office 2016.
I have setup a spreadsheet to monitor my staff's leave allocation, deduct the appropriate hours and show the balance remaining. As an example please see the following.

Staff member has been allocated 134:26 hours leave. He wishes to take some leave, days between Monday and
Saturday work out at 10hrs 46 mins and a Sunday at 10hrs 18mins. In cell F7 I want to multiply the number of days,
D7 etc, by 10:46 and in cell G8, the numbert of days by 10:18. When I do this I get the #VALUE message.
Finally, the total taken will be deducted from J4 with the balance shown in J22.

# Re: Multiply Hours and Minutes by a number

Thank you for the file. Excel doesn't recognize @10:46 in, thus an error

``=IF(B7=0," ",D7*@10:46)``

You may use

``````=IF(B7=0," ",D7*"10:46")

or more reliable

=IF(B8=0," ",D8*TIME(11,47,0))``````

# Re: Multiply Hours and Minutes by a number

Hello Sergei,
Thank you for your response. I used your recommended formula IF(B8=0," ",D8*TIME(11,47,0)) and it worked perfectly. May I ask, why have you set the hours and minutes as 11,47,0? Every example I had seen up to now has recommended that time is shown as 11:47.
Thank you again for your assistance.
best response confirmed by Baz286 (New Contributor)
Solution

# Re: Multiply Hours and Minutes by a number

TIME() function requires all 3 parameters, you can't miss seconds. Please check TIME function - Office Support (microsoft.com)

# Re: Multiply Hours and Minutes by a number

Hello Aergei,
Thank you for explaining that for me. It makes things much clearer and explains why my formula wouldn't work.

# Re: Multiply Hours and Minutes by a number

@Baz286 , you are welcome, glad to help