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)
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
Would it be easier to convert all my time to straight minutes?
:45=45
1:20=80
1:45=105
:45=45
1:20=80
1:45=105
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