| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Hours Minutes Seconds Please
Hi Everyone,
I have this Bit of Code that Outputs the Total Processing Time of a Macro … Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss") … in the Format 00:09:36 which Works Great. What I would like is for it to Output with the Words Hours, Minutes and Seconds … 00 Hours 09 Minutes 36 Seconds. I have Tried Using the "&" and Text But to No Avail. Any Help will be Appreciated. Thanks in Advance. Paul *** Sent via Developersdex http://www.developersdex.com *** |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Hours Minutes Seconds Please
Uzytkownik "Paul Black" napisal w wiadomosci ... Hi Everyone, I have this Bit of Code that Outputs the Total Processing Time of a Macro . Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss") . in the Format 00:09:36 which Works Great. What I would like is for it to Output with the Words Hours, Minutes and Seconds . 00 Hours 09 Minutes 36 Seconds. I have Tried Using the "&" and Text But to No Avail. Any Help will be Appreciated. Thanks in Advance. Paul *** Sent via Developersdex http://www.developersdex.com *** you can try to format cell like hh" hours":mm" minurtes":ss" seconds" mcg |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Hours Minutes Seconds Please
Thanks for the Reply Macgru,
I Tried that But Keep Getting a Syntax Error. All the Best. Paul From: Macgru Uzytkownik "Paul Black" napisal w wiadomosci ... Hi Everyone, I have this Bit of Code that Outputs the Total Processing Time of a Macro . Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss") . in the Format 00:09:36 which Works Great. What I would like is for it to Output with the Words Hours, Minutes and Seconds . 00 Hours 09 Minutes 36 Seconds. I have Tried Using the "&" and Text But to No Avail. Any Help will be Appreciated. Thanks in Advance. Paul *** Sent via Developersdex http://www.developersdex.com *** you can try to format cell like hh" hours":mm" minurtes":ss" seconds" mcg *** Sent via Developersdex http://www.developersdex.com *** |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Hours Minutes Seconds Please
Macgru's idea worked fine for me
Sub Test() Dim s As String s = """Hours:""h"" Minutes:""mm"" Seconds:""ss" 'time as value With [a1] ..NumberFormat = s ..Value = Now End With 'time as string Dim str As String str = Format(Now, s) [a2] = str [a1].EntireColumn.AutoFit End Sub Regards, Peter T "Paul Black" wrote in message ... Thanks for the Reply Macgru, I Tried that But Keep Getting a Syntax Error. All the Best. Paul From: Macgru Uzytkownik "Paul Black" napisal w wiadomosci ... Hi Everyone, I have this Bit of Code that Outputs the Total Processing Time of a Macro . Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss") . in the Format 00:09:36 which Works Great. What I would like is for it to Output with the Words Hours, Minutes and Seconds . 00 Hours 09 Minutes 36 Seconds. I have Tried Using the "&" and Text But to No Avail. Any Help will be Appreciated. Thanks in Advance. Paul *** Sent via Developersdex http://www.developersdex.com *** you can try to format cell like hh" hours":mm" minurtes":ss" seconds" mcg *** Sent via Developersdex http://www.developersdex.com *** |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Hours Minutes Seconds Please
I made a function for this purpose that you may find useful.
Function TimeUnitsFromSeconds(ByVal lSeconds As Long) As Variant 'takes an integer number of seconds and gives a 1-D 0-based arrray 'with 4 elements. 'first element hours, second element minutes, third elements seconds 'fourth element will give the combined units as a string '------------------------------------------------------------------- Dim lSecs As Long Dim lMinutes As Long Dim lHours As Long Dim strSeconds As String Dim strMinutes As String Dim strHours As String Dim strTime As String Dim arr(0 To 3) As Variant lHours = lSeconds \ 3600 lMinutes = (lSeconds - (lHours * 3600)) \ 60 lSecs = (lSeconds - (lHours * 3600)) - (lMinutes * 60) arr(0) = lHours arr(1) = lMinutes arr(2) = lSecs If arr(0) = 1 Then strHours = " hr" Else strHours = " hrs" End If If arr(1) = 1 Then strMinutes = " min" Else strMinutes = " mins" End If If arr(2) = 1 Then strSeconds = " sec" Else strSeconds = " secs" End If If arr(0) = 0 Then If arr(1) = 0 Then If arr(2) = 1 Then strTime = "1 second" Else strTime = arr(2) & " seconds" End If Else If arr(2) = 0 Then strTime = arr(1) & strMinutes Else strTime = arr(1) & strMinutes & ", " & arr(2) & strSeconds End If End If Else If arr(1) = 0 Then If arr(1) = 0 Then strTime = arr(0) & strHours Else strTime = arr(0) & strHours & ", " & arr(2) & strSeconds End If Else If arr(2) = 0 Then strTime = arr(0) & strHours & ", " & arr(1) & strMinutes Else strTime = arr(0) & strHours & ", " & arr(1) & strMinutes & _ ", " & arr(2) & strSeconds End If End If End If arr(3) = strTime TimeUnitsFromSeconds = arr End Function RBS "Paul Black" wrote in message ... Hi Everyone, I have this Bit of Code that Outputs the Total Processing Time of a Macro . Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss") . in the Format 00:09:36 which Works Great. What I would like is for it to Output with the Words Hours, Minutes and Seconds . 00 Hours 09 Minutes 36 Seconds. I have Tried Using the "&" and Text But to No Avail. Any Help will be Appreciated. Thanks in Advance. Paul *** Sent via Developersdex http://www.developersdex.com *** |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Hours Minutes Seconds Please
Thanks Everyone for the Replies.
RBS, How would I Incorporate this into my Program Please. One Other Question, I Used this which Works Great :- Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), _ "hh"" Hours"" - mm"" Minutes"" - ss"" Seconds") The thing is that in the Code it has the Line :- Columns("B:D").AutoFit which Also Works Great. Using the Processing Time it Autofits that Column which is NOT what I want. The Length of Information Produced will Vary which is why I have Used the Offset. Is there a Way to get ( Maybe Merge in Some Way ) the Time Output Over 3 Columns without havung the Hours Minutes Seconds in Individual Cells Please. Basically to Output in the Cell But Overlap 3 Columns for Example. Thanks in Advance. All the Best. Paul Help with Hours Minutes Seconds Please From: RB Smissaert I made a function for this purpose that you may find useful. Function TimeUnitsFromSeconds(ByVal lSeconds As Long) As Variant 'takes an integer number of seconds and gives a 1-D 0-based arrray 'with 4 elements. 'first element hours, second element minutes, third elements seconds 'fourth element will give the combined units as a string '------------------------------------------------------------------- Dim lSecs As Long Dim lMinutes As Long Dim lHours As Long Dim strSeconds As String Dim strMinutes As String Dim strHours As String Dim strTime As String Dim arr(0 To 3) As Variant lHours = lSeconds \ 3600 lMinutes = (lSeconds - (lHours * 3600)) \ 60 lSecs = (lSeconds - (lHours * 3600)) - (lMinutes * 60) arr(0) = lHours arr(1) = lMinutes arr(2) = lSecs If arr(0) = 1 Then strHours = " hr" Else strHours = " hrs" End If If arr(1) = 1 Then strMinutes = " min" Else strMinutes = " mins" End If If arr(2) = 1 Then strSeconds = " sec" Else strSeconds = " secs" End If If arr(0) = 0 Then If arr(1) = 0 Then If arr(2) = 1 Then strTime = "1 second" Else strTime = arr(2) & " seconds" End If Else If arr(2) = 0 Then strTime = arr(1) & strMinutes Else strTime = arr(1) & strMinutes & ", " & arr(2) & strSeconds End If End If Else If arr(1) = 0 Then If arr(1) = 0 Then strTime = arr(0) & strHours Else strTime = arr(0) & strHours & ", " & arr(2) & strSeconds End If Else If arr(2) = 0 Then strTime = arr(0) & strHours & ", " & arr(1) & strMinutes Else strTime = arr(0) & strHours & ", " & arr(1) & strMinutes & _ ", " & arr(2) & strSeconds End If End If End If arr(3) = strTime TimeUnitsFromSeconds = arr End Function RBS "Paul Black" wrote in message ... Hi Everyone, I have this Bit of Code that Outputs the Total Processing Time of a Macro . Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss") . in the Format 00:09:36 which Works Great. What I would like is for it to Output with the Words Hours, Minutes and Seconds . 00 Hours 09 Minutes 36 Seconds. I have Tried Using the "&" and Text But to No Avail. Any Help will be Appreciated. Thanks in Advance. Paul *** Sent via Developersdex http://www.developersdex.com *** |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Hours Minutes Seconds Please
Well, you give the function the seconds and it will return the hours,
minutes, seconds and fourthly all 3 together as a string. So, just to give you an example you could use it like this: dim arr dim lSeconds as Long lSeconds = 2011 arr = TimeUnitsFromSeconds(lSeconds) Cells(1) = arr(3) Cells(2) = arr(0) Cells(3) = arr(1) Cells(4) = arr(2) ------------------------------------- I am not sure about your formatting, maybe you want to merge columns B to D. RBS "Paul Black" wrote in message ... Thanks Everyone for the Replies. RBS, How would I Incorporate this into my Program Please. One Other Question, I Used this which Works Great :- Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), _ "hh"" Hours"" - mm"" Minutes"" - ss"" Seconds") The thing is that in the Code it has the Line :- Columns("B:D").AutoFit which Also Works Great. Using the Processing Time it Autofits that Column which is NOT what I want. The Length of Information Produced will Vary which is why I have Used the Offset. Is there a Way to get ( Maybe Merge in Some Way ) the Time Output Over 3 Columns without havung the Hours Minutes Seconds in Individual Cells Please. Basically to Output in the Cell But Overlap 3 Columns for Example. Thanks in Advance. All the Best. Paul Help with Hours Minutes Seconds Please From: RB Smissaert I made a function for this purpose that you may find useful. Function TimeUnitsFromSeconds(ByVal lSeconds As Long) As Variant 'takes an integer number of seconds and gives a 1-D 0-based arrray 'with 4 elements. 'first element hours, second element minutes, third elements seconds 'fourth element will give the combined units as a string '------------------------------------------------------------------- Dim lSecs As Long Dim lMinutes As Long Dim lHours As Long Dim strSeconds As String Dim strMinutes As String Dim strHours As String Dim strTime As String Dim arr(0 To 3) As Variant lHours = lSeconds \ 3600 lMinutes = (lSeconds - (lHours * 3600)) \ 60 lSecs = (lSeconds - (lHours * 3600)) - (lMinutes * 60) arr(0) = lHours arr(1) = lMinutes arr(2) = lSecs If arr(0) = 1 Then strHours = " hr" Else strHours = " hrs" End If If arr(1) = 1 Then strMinutes = " min" Else strMinutes = " mins" End If If arr(2) = 1 Then strSeconds = " sec" Else strSeconds = " secs" End If If arr(0) = 0 Then If arr(1) = 0 Then If arr(2) = 1 Then strTime = "1 second" Else strTime = arr(2) & " seconds" End If Else If arr(2) = 0 Then strTime = arr(1) & strMinutes Else strTime = arr(1) & strMinutes & ", " & arr(2) & strSeconds End If End If Else If arr(1) = 0 Then If arr(1) = 0 Then strTime = arr(0) & strHours Else strTime = arr(0) & strHours & ", " & arr(2) & strSeconds End If Else If arr(2) = 0 Then strTime = arr(0) & strHours & ", " & arr(1) & strMinutes Else strTime = arr(0) & strHours & ", " & arr(1) & strMinutes & _ ", " & arr(2) & strSeconds End If End If End If arr(3) = strTime TimeUnitsFromSeconds = arr End Function RBS "Paul Black" wrote in message ... Hi Everyone, I have this Bit of Code that Outputs the Total Processing Time of a Macro . Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss") . in the Format 00:09:36 which Works Great. What I would like is for it to Output with the Words Hours, Minutes and Seconds . 00 Hours 09 Minutes 36 Seconds. I have Tried Using the "&" and Text But to No Avail. Any Help will be Appreciated. Thanks in Advance. Paul *** Sent via Developersdex http://www.developersdex.com *** |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Hours Minutes Seconds Please
Thanks RB Smissaert,
The Columns are Set to Autofit the Data BEFORE the Output of the Processing Time. I Basically DON'T want this to Change. Instead I would like the Processing Time that is Output to a SINGLE Cell to Just Overlap the Next Cell OR Cells as Necessary. Thanks in Advance. All the Best. Paul RB Smissaert wrote: Well, you give the function the seconds and it will return the hours, minutes, seconds and fourthly all 3 together as a string. So, just to give you an example you could use it like this: dim arr dim lSeconds as Long lSeconds = 2011 arr = TimeUnitsFromSeconds(lSeconds) Cells(1) = arr(3) Cells(2) = arr(0) Cells(3) = arr(1) Cells(4) = arr(2) ------------------------------------- I am not sure about your formatting, maybe you want to merge columns B to D. RBS "Paul Black" wrote in message ... Thanks Everyone for the Replies. RBS, How would I Incorporate this into my Program Please. One Other Question, I Used this which Works Great :- Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), _ "hh"" Hours"" - mm"" Minutes"" - ss"" Seconds") The thing is that in the Code it has the Line :- Columns("B:D").AutoFit which Also Works Great. Using the Processing Time it Autofits that Column which is NOT what I want. The Length of Information Produced will Vary which is why I have Used the Offset. Is there a Way to get ( Maybe Merge in Some Way ) the Time Output Over 3 Columns without havung the Hours Minutes Seconds in Individual Cells Please. Basically to Output in the Cell But Overlap 3 Columns for Example. Thanks in Advance. All the Best. Paul Help with Hours Minutes Seconds Please From: RB Smissaert I made a function for this purpose that you may find useful. Function TimeUnitsFromSeconds(ByVal lSeconds As Long) As Variant 'takes an integer number of seconds and gives a 1-D 0-based arrray 'with 4 elements. 'first element hours, second element minutes, third elements seconds 'fourth element will give the combined units as a string '------------------------------------------------------------------- Dim lSecs As Long Dim lMinutes As Long Dim lHours As Long Dim strSeconds As String Dim strMinutes As String Dim strHours As String Dim strTime As String Dim arr(0 To 3) As Variant lHours = lSeconds \ 3600 lMinutes = (lSeconds - (lHours * 3600)) \ 60 lSecs = (lSeconds - (lHours * 3600)) - (lMinutes * 60) arr(0) = lHours arr(1) = lMinutes arr(2) = lSecs If arr(0) = 1 Then strHours = " hr" Else strHours = " hrs" End If If arr(1) = 1 Then strMinutes = " min" Else strMinutes = " mins" End If If arr(2) = 1 Then strSeconds = " sec" Else strSeconds = " secs" End If If arr(0) = 0 Then If arr(1) = 0 Then If arr(2) = 1 Then strTime = "1 second" Else strTime = arr(2) & " seconds" End If Else If arr(2) = 0 Then strTime = arr(1) & strMinutes Else strTime = arr(1) & strMinutes & ", " & arr(2) & strSeconds End If End If Else If arr(1) = 0 Then If arr(1) = 0 Then strTime = arr(0) & strHours Else strTime = arr(0) & strHours & ", " & arr(2) & strSeconds End If Else If arr(2) = 0 Then strTime = arr(0) & strHours & ", " & arr(1) & strMinutes Else strTime = arr(0) & strHours & ", " & arr(1) & strMinutes & _ ", " & arr(2) & strSeconds End If End If End If arr(3) = strTime TimeUnitsFromSeconds = arr End Function RBS "Paul Black" wrote in message ... Hi Everyone, I have this Bit of Code that Outputs the Total Processing Time of a Macro . Active.Offset(1, 0) = "Processing Time " & _ Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss") . in the Format 00:09:36 which Works Great. What I would like is for it to Output with the Words Hours, Minutes and Seconds . 00 Hours 09 Minutes 36 Seconds. I have Tried Using the "&" and Text But to No Avail. Any Help will be Appreciated. Thanks in Advance. Paul *** Sent via Developersdex http://www.developersdex.com *** |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need to convert 157.25 minutes, into hours, minutes and seconds. | Excel Discussion (Misc queries) | |||
| Converting hours, minutes, seconds, to hours | Excel Worksheet Functions | |||
| Formula to Change Hours:Minutes:Seconds to Seconds only | Excel Discussion (Misc queries) | |||
| Converting hours:minutes:seconds to just minutes | Excel Worksheet Functions | |||
| Convert "Time Interval" in "hours : minutes : seconds" to seconds | New Users to Excel | |||
![Paul Black[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
