Quick help please! Converting seconds to hours and minutes.

[Guest]

I've done some reading, but can't find the same problem as mine exactly.

cell A1 =463237 seconds.

I want to display this as: hh:mm:ss, but not in 24 hour clock. Meaning, I want to see 128 hours:33 minutes: 32 seconds (not proper figures I know).
The formula I am tyring to use is: =TEXT(C3/24,"h:mm:ss"), but it doesn't seem to work the way I want.

Anybody???

 

[Dave Peterson]

How about:

=TEXT(C3/24/60/60,"[h]:mm:ss")

But I got: 128:40:37

 

[Dave Peterson]

And I think I'd just put:

=C3/24/60/60

in the cell and format it as [h]:mm:ss

Then the cell would still be a number and I could do further calculations with
it.

 

[David McRitchie]

In Excel time is a fraction of a day so you would have to divide by 24
there are 86400 seconds in a day (24*60*60) so your number
actually represents a few days.
=A1/86400 format as [d] hh:mm:ss if you want to see days
format as [h]:mm:ss if you don't want hours to overflow into days.

 

[Guest]

most excellent. Thanks guys!

In Excel time is a fraction of a day so you would have to divide by 24
there are 86400 seconds in a day (24*60*60) so your number
actually represents a few days.
=A1/86400 format as [d] hh:mm:ss if you want to see days
format as [h]:mm:ss if you don't want hours to overflow into days.

I've done some reading, but can't find the same problem as mine exactly.

cell A1 =463237 seconds.

I want to display this as: hh:mm:ss, but not in 24 hour clock. Meaning, I want to see 128 hours:33 minutes: 32 seconds (not proper figures I know).
The formula I am tyring to use is: =TEXT(C3/24,"h:mm:ss"), but it doesn't seem to work the way I want.

Anybody???

 

[David McRitchie]

Hi Charlie,
I'm surprised I guess just use
d hh:mm:ss
good up to 31 days.

Hi David,

I Tried to put in the Format [d] hh:mm:ss, but Excel 2000 does not except
it. Is this format for a version of Excel above 2000 and would you know a
work around for Excel 2000? Any help you can provide would be greatly
appreciated.

Thanks, Charlie

In Excel time is a fraction of a day so you would have to divide by 24
there are 86400 seconds in a day (24*60*60) so your number
actually represents a few days.
=A1/86400 format as [d] hh:mm:ss if you want to see days
format as [h]:mm:ss if you don't want hours to overflow into days.

I've done some reading, but can't find the same problem as mine exactly.

cell A1 =463237 seconds.

I want to display this as: hh:mm:ss, but not in 24 hour clock. Meaning, I want to see 128 hours:33 minutes: 32 seconds (not proper figures I know).
The formula I am tyring to use is: =TEXT(C3/24,"h:mm:ss"), but it doesn't seem to work the way I want.

Anybody???

 

[Guest]

Hi David,

Many thanks for the reply. I am new to this style forum so it took me a
few days to get your reply even though you answered me back right away.
Anyway that solution worked. Now if I may I have another question.

Say I have cell A1=13:51:41.1 (24 hour time) I would like to add say 3
hours 45 minutes to that time and redisplay the new time. Is this possible in
Excel 2000 or will it require a visual basic macro or some other method to
solve it? Any help would be appreciated.

Thanks, Charlie

Hi Charlie,
I'm surprised I guess just use
d hh:mm:ss
good up to 31 days.

Hi David,

I Tried to put in the Format [d] hh:mm:ss, but Excel 2000 does not except
it. Is this format for a version of Excel above 2000 and would you know a
work around for Excel 2000? Any help you can provide would be greatly
appreciated.

Thanks, Charlie

In Excel time is a fraction of a day so you would have to divide by 24
there are 86400 seconds in a day (24*60*60) so your number
actually represents a few days.
=A1/86400 format as [d] hh:mm:ss if you want to see days
format as [h]:mm:ss if you don't want hours to overflow into days.


I've done some reading, but can't find the same problem as mine exactly.

cell A1 =463237 seconds.

I want to display this as: hh:mm:ss, but not in 24 hour clock. Meaning, I want to see 128 hours:33 minutes: 32 seconds (not
proper figures I know).
The formula I am tyring to use is: =TEXT(C3/24,"h:mm:ss"), but it doesn't seem to work the way I want.

Anybody???

 

[David McRitchie]

Hi Charlie,
It is not clear how you want to add 3hr 45min to cell A1.

A1: =C1 + "3:45"
A1: =C1 + time(3,45,0)
A1: =C1+D1

the last would be preferable as people want/need to know where the
numbers come from.

If you want to add 3:45 to anything entered in A1 you would need
an Event macro. RightClick on sheetname, view code, insert your code.
To make it effective for anything in column A except row 1, remove if target.column line.
But as I indicated people aren't going to like seeing figures that
they don't know where they came from.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.row <> 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False 'should be part of Change macro
Target.Value = Target.Value + TimeSerial(3, 45, 0)
Application.EnableEvents = True 'should be part of Change macro
End Sub

More information on Event Macros in
http://www.mvps.org/dmcritchie/excel/event.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi David,

Many thanks for the reply. I am new to this style forum so it took me a
few days to get your reply even though you answered me back right away.
Anyway that solution worked. Now if I may I have another question.

Say I have cell A1=13:51:41.1 (24 hour time) I would like to add say 3
hours 45 minutes to that time and redisplay the new time. Is this possible in
Excel 2000 or will it require a visual basic macro or some other method to
solve it? Any help would be appreciated.

Thanks, Charlie

Hi Charlie,
I'm surprised I guess just use
d hh:mm:ss
good up to 31 days.

Hi David,

I Tried to put in the Format [d] hh:mm:ss, but Excel 2000 does not except
it. Is this format for a version of Excel above 2000 and would you know a
work around for Excel 2000? Any help you can provide would be greatly
appreciated.

Thanks, Charlie

:

In Excel time is a fraction of a day so you would have to divide by 24
there are 86400 seconds in a day (24*60*60) so your number
actually represents a few days.
=A1/86400 format as [d] hh:mm:ss if you want to see days
format as [h]:mm:ss if you don't want hours to overflow into days.


I've done some reading, but can't find the same problem as mine exactly.

cell A1 =463237 seconds.

I want to display this as: hh:mm:ss, but not in 24 hour clock. Meaning, I want to see 128 hours:33 minutes: 32 seconds (not
proper figures I know).
The formula I am tyring to use is: =TEXT(C3/24,"h:mm:ss"), but it doesn't seem to work the way I want.

Anybody???

 

[Guest]

Hey David,

Wow that is exactly what I was looking for! Thank you Very much for
taking the time to reply to me and even a bigger THANKS for givong me a
solution for what I asked! Anyway I do appreciate your effort as it will now
be possible for me to complete my project. Hope you have a Great Day! Thanks
to you I will be enjoying the rest of mine.

Many Thanks!,
Charlie

Hi Charlie,
It is not clear how you want to add 3hr 45min to cell A1.

A1: =C1 + "3:45"
A1: =C1 + time(3,45,0)
A1: =C1+D1

the last would be preferable as people want/need to know where the
numbers come from.

If you want to add 3:45 to anything entered in A1 you would need
an Event macro. RightClick on sheetname, view code, insert your code.
To make it effective for anything in column A except row 1, remove if target.column line.
But as I indicated people aren't going to like seeing figures that
they don't know where they came from.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.row <> 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False 'should be part of Change macro
Target.Value = Target.Value + TimeSerial(3, 45, 0)
Application.EnableEvents = True 'should be part of Change macro
End Sub

More information on Event Macros in
http://www.mvps.org/dmcritchie/excel/event.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi David,

Many thanks for the reply. I am new to this style forum so it took me a
few days to get your reply even though you answered me back right away.
Anyway that solution worked. Now if I may I have another question.

Say I have cell A1=13:51:41.1 (24 hour time) I would like to add say 3
hours 45 minutes to that time and redisplay the new time. Is this possible in
Excel 2000 or will it require a visual basic macro or some other method to
solve it? Any help would be appreciated.

Thanks, Charlie

Hi Charlie,
I'm surprised I guess just use
d hh:mm:ss
good up to 31 days.


Hi David,

I Tried to put in the Format [d] hh:mm:ss, but Excel 2000 does not except
it. Is this format for a version of Excel above 2000 and would you know a
work around for Excel 2000? Any help you can provide would be greatly
appreciated.

Thanks, Charlie

:

In Excel time is a fraction of a day so you would have to divide by 24
there are 86400 seconds in a day (24*60*60) so your number
actually represents a few days.
=A1/86400 format as [d] hh:mm:ss if you want to see days
format as [h]:mm:ss if you don't want hours to overflow into days.


I've done some reading, but can't find the same problem as mine exactly.

cell A1 =463237 seconds.

I want to display this as: hh:mm:ss, but not in 24 hour clock. Meaning, I want to see 128 hours:33 minutes: 32 seconds (not
proper figures I know).
The formula I am tyring to use is: =TEXT(C3/24,"h:mm:ss"), but it doesn't seem to work the way I want.

Anybody???

 

[David McRitchie]

Thanks, though it does make me curious if the wanted solution
was one of the three worksheet formulas,
The fill handle, or
the Change Event macro.

 

[Guest]

Hey David,

Let me try this again. Sorry I should have explained more clearly from
the start. To be honest I didn't think I was going to get anyone that would
be willing to help. So let me give you the details.

I have put 3 Radio buttons on my sheet. Each will hopefully add a
specified amount of time to get the end result I am looking for. So what I
need is when I click on say radio button B1. I want to take the (time
formated hh:mm:ss.0) value in cell B12 add 3 hours and 24 minutes to it and
place the resulting (time formatted hh:mm:ss.0) value of 17:15:41.1 and place
it in say cell H11. Is one of the solutions you sent me able to do that or do
I need to ask for something new? I can't tell you how much I appreciate your
help. If you need further explenation please let me know. I hope I have
explained it better this time.

Many Thanks,
Charlie

 

[David McRitchie]

Thanks, Got it. It is the macro solution that is going to be most helpful, because
that is what you will need parts of for a macro to go with radio buttons.

 

[Guest]

Hey David,

Thanks for the clarification. Only one more question if you don't mind.

How do I create a macro to recognize a Radio Button. I am pretty good
with Visual C++, but haven't had much experience with Visual Basic Macros. So
any help would be appreciated.

Thanks Again,
Charlie