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%3CLINGO-SUB%20id%3D%22lingo-sub-2273946%22%20slang%3D%22en-US%22%3EBetreff%3A%20Multiply%20Hours%20and%20Minutes%20by%20a%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2273946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1025869%22%20target%3D%22_blank%22%3E%40Baz286%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20read%20this%20information.%20It%20is%20of%20great%20help%20to%20you%20and%20everyone%20who%20would%20like%20to%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwelcome-to-your-excel-discussion-space%2Fm-p%2F2204395%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwelcome-to-your-excel-discussion-space%2Fm-p%2F2204395%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20if%20I%20may%20recommend%20you%2C%20add%20a%20file%20(without%20sensitive%20data)%20and%20explain%20exactly%20what%20your%20plan%20is.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2274136%22%20slang%3D%22en-US%22%3ERe%3A%20Multiply%20Hours%20and%20Minutes%20by%20a%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2274136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1025869%22%20target%3D%22_blank%22%3E%40Baz286%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20it%20works%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20332px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F273010iE84C43221B9850C4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20have%20text%20somewhere.%20Better%20if%20you%20could%20provide%20simple%20sample%20file%20to%20check.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2275175%22%20slang%3D%22en-US%22%3ERe%3A%20Multiply%20Hours%20and%20Minutes%20by%20a%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2275175%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20an%20Acer%20Aspire%205%20laptop%20running%20Windows%2010%20version%2020H2%2C%20Microsoft%20Office%202016.%3CBR%20%2F%3EI%20have%20setup%20a%20spreadsheet%20to%20monitor%20my%20staff's%20leave%20allocation%2C%20deduct%20the%20appropriate%20hours%20and%20show%20the%20balance%20remaining.%20As%20an%20example%20please%20see%20the%20following.%3C%2FP%3E%3CP%3EStaff%20member%20has%20been%20allocated%20134%3A26%20hours%20leave.%20He%20wishes%20to%20take%20some%20leave%2C%20days%20between%20Monday%20and%3CBR%20%2F%3ESaturday%20work%20out%20at%2010hrs%2046%20mins%20and%20a%20Sunday%20at%2010hrs%2018mins.%20In%20cell%20F7%20I%20want%20to%20multiply%20the%20number%20of%20days%2C%3CBR%20%2F%3ED7%20etc%2C%20by%2010%3A46%20and%20in%20cell%20G8%2C%20the%20numbert%20of%20days%20by%2010%3A18.%20When%20I%20do%20this%20I%20get%20the%20%23VALUE%20message.%3CBR%20%2F%3EFinally%2C%20the%20total%20taken%20will%20be%20deducted%20from%20J4%20with%20the%20balance%20shown%20in%20J22.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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.

 

10 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 (Occasional 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

@Sergei Baklan 

Hello again,
May I seek your advice once again regarding calculating hours and minutes.
I have been taking your advice regarding calculating time however, on addition, it does not calculate properly.
I am using Windows 10 Pro, Excel 2016.
I have attached a file showing my calculations. I would welcome your advice on adapti g the correct formula.

Regards,
Barry Nelson

@Baz286 

Barry, formulas looks correct, I'd only not to hardcode constants like TIME(10,46,0) and keep then in separate cells somewhere outside. Otherwise you need to correct every formula if the constant changes.

 

Sure, everything in Excel could be done by few different ways. Stay on formulas you are more familiar with if they give correct results.