SOLVED

Multiply Hours and Minutes by a number

%3CLINGO-SUB%20id%3D%22lingo-sub-2273867%22%20slang%3D%22en-US%22%3EMultiply%20Hours%20and%20Minutes%20by%20a%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2273867%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20setup%20a%20spreadsheet%20to%20monitor%20my%20staff's%20holiday%20hours.%20I%20have%20formatted%20the%20cells%20using%20%5Bh%5D%3Amm%20and%20also%20used%20the%201904%20date%20system.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%2C%20when%20trying%20to%20multiply%20the%20hours%20by%20the%20number%20of%20days%20taken%2C%20e.g.%2010hrs%2046mins%20by%202%20I%20get%20a%20Value%20message%20in%20the%20cell.%20I%20formula%20I%20use%20is%20(A3%20equals%2010%3A46)%20%3DA3*2%2C%20can%20anyone%20advise%20me%20of%20the%20correct%20formula%20to%20use%3F%20Any%20assistance%20would%20be%20gratefully%20received.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2273867%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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

@Baz286 

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

@Baz286 

In general it works

image.png

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

 

@Sergei Baklan 

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.

@Baz286 

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))
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

@Baz286 

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

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

@Baz286 , you are welcome, glad to help