Formula to convert formated date to a number

[tav1035]

I have a maintenance software that exports data into excel as .csv.
When the estimated hour and actual hours export they look like the following example.
* The first column is the data just as it appears in excel.
* The second column is the format (right clicking on the cell and noting the format)
* The third column is a copy and paste straight from the formula bar.

ACTLABHRS Format FormulaBar
16:00 h:mm 4:00:00 PM
2:00 h:mm 2:00:00 AM
0:00 h:mm 12:00:00 AM
0:00 h:mm 12:00:00 AM
22:00 h:mm 10:00:00 PM
69:30:00 [h]:mm:ss 1/2/1900 9:30:00 PM
82:30:00 [h]:mm:ss 1/3/1900 10:30:00 AM
1834:00:00 [h]:mm:ss 3/16/1900 10:00:00 AM

Yes 82:30:00 is actually 82hrs and 30mins.
I would like it to look like 82.30 without the appended zero's.
Is this possible with a formula?

In the maintenance software, where the data comes from, the ACTLABHRS field is a time stamp. If you input 82.5, it converts to 82.30 when you tab out of the field.

Any suggestions?
Thanks,
tav

 

[SkipVought]

Hi,

Custom in Format > Cells - Number tab...
[tt]
[h]:mm
[/tt]



Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[AnotherHiggins]

I'd point you to faq68-5827 (which Skip, the respondent above, wrote) to help explain why no formula is needed, just a change of format.

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

Help us help you. Please read FAQ 181-2886 before posting.

 

[tav1035]

I was hoping to get rid of the time stamp all together. It's a pain. When I double click into the cell the time format appears and the 82:30 becomes a date.
Also it's hard to run conditions and formulas from that cell.
example: filter on jobs with actuals less than 5hr... etc.
I was hoping to copy and paste as values, but that doesn't work here.
I'm using format [h].mm
A period kinda displays what I need.
thanks.
tav

 

[xlbo]

Your problem is that dates & times are linked (as per Skip's FAQ). Time is represented as the decimal portion of a day. The fact that it converts to a "Date" is neither here nor there - it is simply formatting

Have you tried filtering for < 5/24 ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"I was hoping to get rid of the time stamp all together. It's a pain."

"example: filter on jobs with actuals less than 5hr... etc."

Do you really understand what you are doing? Your statements seem to be somewhat contradictory.

Did you read and comprehend the FAQ on Date/Time? It is ESSENTIAL to understand.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}