Forum Discussion

Baz286's avatar
Baz286
Copper Contributor
Apr 15, 2021
Solved

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 ...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 15, 2021

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))
ProductionTech's avatar
ProductionTech
Copper Contributor
Aug 24, 2023

SergeiBaklanI am trying to do something similar.  I want to calculate the total time to assemble

 

19 pcs * :45 minutes each unit

 

How do I get it to display correctly?

 

Windows 11 Pro

Version 22H2

Office 365

Version 2307 Build16626.20170

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 24, 2023

    ProductionTech 

    In Excel datetimes are actually numbers where days are integer part of the number and time is decimal one. One day is equal to 1.

    Thus 1 hour = 1/24

    1 minute =1/24/60

    etc

    45 min will be 45/24/60. Multiply on 19, totally =19*45/24/60 and apply time format. It returns 14:15:00.

    • ProductionTech's avatar
      ProductionTech
      Copper Contributor
      Aug 24, 2023
      Our times are in ":00" format or "0:00". Can I use the following formula where M13=:45 and C13=19
      =(M13/24/60)*C13
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 24, 2023

        ProductionTech 

        It looks like in M13 you have text which looks like a time, not the time actually (which shall be a number). Could you please check with formulae as

        what do they return. If the same what are possible variants for such texts.

Resources