convert HH:MM:SS to an integer

  • Thread starter Thread starter rjc_29
  • Start date Start date
R

rjc_29

Can you tell me how to convert a time format of hh:MM:SS to an intege
value within excel e.g. 11:01:01 to 1101.

Ideally i would like to run it as a macro as i have a timefrom and
timeto column in my spreadsheet which can contain alot of records
 
Hi

Multiply the time by 24 and format the result as general.

Andy.

rjc_29 said:
Can you tell me how to convert a time format of hh:MM:SS to an integer
value within excel e.g. 11:01:01 to 1101.

Ideally i would like to run it as a macro as i have a timefrom and a
timeto column in my spreadsheet which can contain alot of records.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Thank you for the suggestion but not the format i need it as tried tha
and looks like 11.01694444
i want it to show as 1101.

Someone mentioned using mid string cell no, 1,2
+ mid$(cell no, 4,2).

or converting to text string then changing it to a value>example of ho
need time to look is:
time 03:55:00 numeriacal/integer value 355
time 11:55:00 numeriacal/integer value 1155


Any suggestions of a formula that i can use in a macro in excel pleas
let me know
 
Actually, to get 1102, you can't just multiply by 24 - that'll just give youthe number of days. To convert 11:02 to 1102, you can use this
=VALUE(REPLACE(TEXT(A1,"hh:mm"),FIND(":",TEXT(A1,"hh:mm")),1,""))
 
Sorry.

With the time in F10, try this:

=HOUR(F10)+(MINUTE(F10)/100)

Andy

rjc_29 said:
Thank you for the suggestion but not the format i need it as tried that
and looks like 11.01694444
i want it to show as 1101.

Someone mentioned using mid string cell no, 1,2
+ mid$(cell no, 4,2).

or converting to text string then changing it to a value>example of how
need time to look is:
time 03:55:00 numeriacal/integer value 355
time 11:55:00 numeriacal/integer value 1155


Any suggestions of a formula that i can use in a macro in excel please
let me know.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Can you tell me how to convert a time format of hh:MM:SS to an integer
value within excel e.g. 11:01:01 to 1101.

Ideally i would like to run it as a macro as i have a timefrom and a
timeto column in my spreadsheet which can contain alot of records.


------------------------------------------------
Click to expand...


If you just want to display it as 1101, then you can use a custom format.

Select the cell(s). Then Format/Cells/Number/Custom/Type: hhmm

If you want it to BE the integer, 1101, then the formula:

=INT(dollarfr(A1*24,60)*100)

should do the conversion.

If the DOLLARFR function is not available, and returns the #NAME? error,
install and load the Analysis ToolPak add-in.

Or, without the ATP, you can use the formula:

=INT(A1*24)*100+INT(MOD(A1*24,1)*60)




--ron
 
Ron

The =INT(DOLLARFR(AS2*24,60)*100) works well - thank you. How would
get it to run in a macro to convert all tha time data contained in tw
separate columns AL:AL and AO:AO within an excel spreadsheet sheet
 
Ron

The =INT(DOLLARFR(AS2*24,60)*100) works well - thank you. How would i
get it to run in a macro to convert all tha time data contained in two
separate columns AL:AL and AO:AO within an excel spreadsheet sheet.
Click to expand...

Although it could be used in a macro, I wouldn't use that formula that way.

To use that formula, I would insert a column next to AL. I would then use that
formula in the column, and copy down. Do an Edit/Copy/Paste Special/Values to
replace the formula with the values, and then delete the unneeded column.

To use a macro, I would use a different formula and I would use the following
macro:

========================
Sub ConvertTime()
Dim c As Range
For Each c In Selection
If IsNumeric(c.Value) Then
c.Value = Int(c.Value * 24) * 100 + _
Int(60 * (c.Value * 24 - Int(c.Value * 24)))
c.NumberFormat = "0000"
End If
Next c
End Sub
=======================

Then you select the range that needs to be converted, and run the macro.

***Be sure to backup your data before doing this.***

It is certainly possible to use ATP functions in a VBA macro, but in this case,
it's easier to use a non-ATP requiring formula.


--ron
 
Back
Top