Convert HH:MM:SS to decimal 2

[SuperNECtech]

I can find thousands of examples of changing the other way, but I want to change a duration, measured in HH:MM:SS to a decimal. Any help?

Lance

 

[TonyJollans]

Decimal what? Hours? Days?

And how exactly is your HH:MM:SS formatted? A single string? Separate strings for each component? Separate numbers for each component?

And in what Application? Excel? Access? Word?


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SuperNECtech]

HH:MM:SS is a duration of time, not a time of day, I am using Microsoft Office 2003 Excel and when I insert the duration, Excel assumes it is a time of day (argh!)
Example is 1 minute 24 seconds is represented as 00:01:24

 

[AnotherHiggins]

I suggest you read faq68-5827, "Why do Dates and Times seem to be so much trouble?" for some info on how Excel deals with dates and times.

Excel will deal with 00:01:24 as a duration just fine. Why do you need to change it? Be aware that changing it to a decimal will prevent you from doing proper arithmetic on the durations.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SuperNECtech]

I want to take the duration of time and multiply it by a cost of $.06 per minute. When I try this in Excel, it doesn't come out in the proper format. eg: a duration of 10 minutes 8 seconds which is represented by 00:10:08, x $.06 comes out as .0004 instead of $.6079999 or 61 cents

 

[dhulbert]

If you use

=(HOUR(B4)*60)+(MINUTE(B4))+(SECOND(B4)/60)

if you put 04:30:30 in cell B4 you get the result 270.5 multiply that by your rate per minute and you're done.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[AnotherHiggins]

This is, IMHO, a little easier:
[tab][COLOR=blue white]=A1*24*B1[/color]
where A1 is the time and B1 is the rate per minute.

Post back with any questions about why that works that aren't answered in FAQ68-5827.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

IF, IF, IF...

you understand that Excel TIME values are in units of DAYS, then you can CONVERT Days to Hours Or Days to Minutes or Days to Seconds, knowing that there are 24 Hours per Day, 60 Minutes per Hour and 60 Seconds per Minute.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Wray69]

Can give this a try;

=(B1-INT(B1))*24

Cheers,

Wray

 

[AnotherHiggins]

Wray69,

That is a fine way to get time-only information, but it isn't required in this case.

when I insert the duration, Excel assumes it is a time of day

Lance is inputting the times himself, so there will be no date info associated with it.

My previous suggestion of [COLOR=blue white]=A1*24*B1[/color] will suffice.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[TonyJollans]

I haven't read Skip's FAQ in ages but I'm sure it gives you all you need to know, and more. You really do need to understand how Excel handles dates and times before doing serious work with them. However ...

... in this case, provided you understand that they are numbers and that the display of hh:mm or whatever is just a format, you don't need to know any more than that. 0.06, by itself, is not a rate per minute, it's just a number - how is Excel to know it's per minute and not per second, or per hour? The answer is that you have to tell it - 0.06/"00:01:00" is 6 cents per (which means divided by) minute; 0.06/"01:00:00" would be 6 cents per hour, etc.

So, multiply your time by your rate per minute, properly expressed, and you will get a number. Excel may, by default, format that number as a time and that might confuse you at first glance, but format it as dollars and cents and you will have your answer.

John[//b],

=A1*24*B1 will not give the right answer - it needs multiplying by another 60.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Wray69]

Anotherhiggins,

Sorry I didnt see Lance's other post describing what he wanted. I didnt get your post to work correctly either though. I did get this to work though. If my time is in B1 and I use this formula I seem to get the correct answer;

=((HOUR(B1)*60)+(MINUTE(B1))+(SECOND(B1)/60))*0.06

Cheers,

Wray

 

[Wray69]

Tony's Answer is allot easier then mine.

Good Job Tony

 

[AnotherHiggins]

Tony is correct, of course.

As has been stated, time is stored in units of days. If you just entered 10:08, Excel would assume that is 10 hrs and 8 minutes. Multiplying that by 24 hours would be correct.

But since you have 00:10:08 entered (10 minutes and 8 seconds), you need to multiply by 24 (hours in a day) by 60 (minutes in an hour).

So =A1*24*60*B1 would work. Or, to simplify, =A1*1440*B1.

Of course Tony's solution works as well and might seem more logical to you.

As Skip is fond of saying, there are many ways to skin a cat.

But one thing we all agree on: you should get to understand how Excel deals with dates and times.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.