In Google Sheets, how do I multiply a duration or interval constant?-CodePudding

I'm making calculations on production cost (in number of resources) and duration.

I have a process that takes 5 minutes. Using the Duration format, I would enter that as 00:05:00.

I want to queue up this process a certain number of times and calculate the total duration. The output should either be something like 16:35:00 or 5 02:15:00. A "d HH.mm.ss" format.

How, in Google Sheets, do I multiply a Duration by an integer to get another Duration?

All these attempts resulted in Formula Parse Error:

=(5*00:05:00)
=(112*00:05:00.000)
=(VALUE(C27)*00:05:00)
=MULTIPLY(VALUE(C27),00:05:00.000)

Well, blow me down. I came up with a workaround while I was trying different ways to fail. I assigned 00:05:00 to it's own cell with the Duration format, then referenced that cell in the formula. I.E. =C27*J7 gives me 9:20:00 when C27 equates to 112 (it's a summation of it's own) and J7 is the cell holding 00:05:00.

Still doesn't give me days when it goes over 24 hours, and I'd rather have the duration value as a constant in the formula, but it's a step forward.

CodePudding user response：

Would something like this work for you?? It's no longer a number, but if it's for expressing the amount in your desired format it may be useful:

=IF(ROUNDDOWN(W2*W3),ROUNDDOWN(W2*W3)&"d "&TEXT(W2*W3-ROUNDDOWN(W2*W3),"hh:mm:ss"),TEXT(W2*W3,"hh:mm:ss"))

Change the cell references, obviously

PS: If you want to have the value as a constant in your formula, you can try to change the cell reference with TIME function within your formula:

CodePudding user response：

In both Excel and Google spreadsheet, DATE are represented in a number start counting from 1899/12/30,

which...

1 is equal to 1 day

1/24 is equal to 1 hour

1/24/60 is equal to 1 minute

1/24/60/60 is equal to 1 second

you can do like:

=TODAY() 1 which gives you tomorrow, or...

=TODAY() 12/24 which gives you "date of today" 12:00:00

and when you are done with the calculations, you can simply use a TEXT() to format the NUMBER back into DATE format, such as:

=TEXT(TODAY() 7  13/24  15/24/60,"yyyy-mm-dd hh:mm:ss")

will return the date of a week away from today at 01:15:00 p.m.

This date/time format doesn't requires a full date to work, you can get difference of two time format like this:

=TEXT(1/24/60 - 1/24/60/60,"hh:mm:ss")

since 1/24/60 is 1 min, and 1/24/60/60 is 1 second, this formula returns 00:00:59, telling you that there is a 59 seconds diff. between 1 min and 1 sec.