How to convert a time stamp to a number

[kpinto]

Data I downloaded as a csv file has the information in a time stamp format

[TABLE="width: 192"]


<colgroup><col width="64" style="width: 48pt;" span="3">
<tbody>[TR]

[TD="width: 64, bgcolor: transparent"]ON-DUTY[/TD]

[TD="width: 64, bgcolor: transparent"]DRIVING[/TD]

[TD="width: 64, bgcolor: transparent"]TOTAL[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2:48[/TD]

[TD="class: xl65, bgcolor: transparent, align: right"]9:40[/TD]

[TD="class: xl65, bgcolor: transparent, align: right"]12:28[/TD]

[/TR]


</tbody>[/TABLE]
How do I covert this data to a number?

 

[Saba Sabaratnam]

I am not 100% sure by your question.

If you want to convert time into number, multiply it by 24 and format it as general number. If you do, for example, 2.48 will become 2.80 as 48 minutes in term of general number is equal to .8 (48 minutes / 60 minutes).

Kind regards

Saba

 

[Robby19]

You could multiply each one by 24. For example;

=A2*24

or

=HOUR(A2)+MINUTE(A2)/60+SECOND(A2)/3600

Both would give you the same answer. Additionally, make sure you format the formulas cell as General.

 

[kpinto]

I am not 100% sure by your question.

If you want to convert time into number, multiply it by 24 and format it as general number. If you do, for example, 2.48 will become 2.80 as 48 minutes in term of general number is equal to .8 (48 minutes / 60 minutes).

Kind regards

Saba

Thank you very much

 

[kpinto]

A quick question

The data is reported as a time stamp . If I use this formula I get the result with a conversion considering the time. What I'm looking for is just to convert the time stamp field to a full number. Thanks

 

[Saba Sabaratnam]

You mean that you want to round up the number.

For example, what would be the output for times stamp 2:48 if you convert it to 'full number' as you are looking to do?

If you give the output that you are looking for, we can put a formula to do it.

Kind regards

Saba

 

[kpinto]

Sorry for not providing clarity

What I see in the excel cell is 2:48:00 PM

What I want is to convert this to a full number 2.48

Thanks
Keith

You mean that you want to round up the number.

For example, what would be the output for times stamp 2:48 if you convert it to 'full number' as you are looking to do?

If you give the output that you are looking for, we can put a formula to do it.

Kind regards

Saba

 

[Saba Sabaratnam]

Hi Keith,

Copy your data to A1:C1 to understand the formula

Enter the following formula in A2 and copy it across to C2

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

Kind regards

Saba