Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Multiply static time by variable number in minute / seconds

Posted on 2012-04-10
2
295 Views
Last Modified: 2012-04-10
Hello Experts,

I always get confused on getting the correct time it requires to perform a task based on a set number and keep in minute/second format.
I need the outcome to be in the minute:second format 0:00:00
A1 is the time to perform the task (in this case 0:01:04)
A2 is the variable number to enter for the count of tasks to be completed (scenerio is 24)
(A2 will be keyed by end-user and is a variable number)
In my example 24 x 0:01:04 = 0:25:36

I am not sure if this is an excel expression or a vba change cell calcuation to derive this result.

Any assistance would be appreciated since I am stuck and need to use this format for numerous task activities.

Thanks,
Michael
0
Comment
Question by:mike637
  • 2
2 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 37827744
Hello Michael,
There may be an easier way but this seems to work.  In my formula A1 contains the hh:mm:ss.  B1 contains the multiplier.  Here's the formula.

=TEXT(B1*SUM(HOUR(A1)*60^2,MINUTE(A1)*60,SECOND(A1))/86400,CHOOSE(MATCH(B1*SUM(HOUR(A1)*60^2,MINUTE(A1)*60,SECOND(A1)),{0,60,3600},1),":ss","m:ss","[h]:mm:ss"))

Kyle
0
 
LVL 12

Accepted Solution

by:
kgerb earned 500 total points
ID: 37827763
If you want the format to be consistent then you can this.  It's shorter.

=TEXT(B1*SUM(HOUR(A1)*60^2,MINUTE(A1)*60,SECOND(A1))/86400,"hh:mm:ss")
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question