Forum Discussion
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 ...
TIME() function requires all 3 parameters, you can't miss seconds. Please check TIME function - Office Support (microsoft.com)
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.
It is as you say:
TRUE=ISTEXT(M13)
#VALUE=TIMEVALUE(M13)
I've had some luck with my own formulas if they come back with an even hour. It doesn't like fractions of an hour or minutes.
- =C22*MMULT( IFERROR(TEXTSPLIT(Y22, ":") + 0, 0), VSTACK(1 / 24, 1 / 24 / 60) )
Where C22=35
Y22=:45 - Okay, I have an error if it totals over 24 hours. It will just return anything over 24:00
:45 per unit x 35 units = 2:15 ProductionTech , you are welcome
- This worked. Thank You!
=C13 *
MMULT(
IFERROR(TEXTSPLIT(M13, ":") + 0, 0),
VSTACK(1 / 24, 1 / 24 / 60)
) - I have over 20,000 parts but I'm just getting started entering the times. What I mean is, I can enter the data in minutes instead of the h:mm format
- Would it be easier to convert all my time to straight minutes?
:45=45
1:20=80
1:45=105 When we need to convert texts to numbers first. Variants could be
=C13 * IF( SEARCH(":", M13) - 1, LEFT(M13, SEARCH(":", M13) - 1) / 24 + MID(M13, SEARCH(":", M13) + 1, 5) / 24 / 60, MID(M13, SEARCH(":", M13) + 1, 5) / 24 / 60 )or if you are on 365
=C13 * MMULT( IFERROR(TEXTSPLIT(M13, ":") + 0, 0), VSTACK(1 / 24, 1 / 24 / 60) )or something like that