Forum Discussion
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.
TIME() function requires all 3 parameters, you can't miss seconds. Please check TIME function - Office Support (microsoft.com)
25 Replies
In general it works
Perhaps you have text somewhere. Better if you could provide simple sample file to check.
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.- 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.
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
