Converting decimal values in to seconds [ss]

hechesspee

New Member
Joined
Aug 16, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi,
I'm looking to convert the below data with decimals in to seconds.
Column-->Data-->Desired format
A1-->0.3 --> 30
A2-->0.03 --> 03
A3-->1.3 --> 90
A4-->40.3 --> 2430

I used formula =A1/864 and used format [ss] and it gave me the correct result when the value is less than 1 i.e up to 0.59
When it is above 1 for example 1.3, it is converting into 130, not 90.

Can someone please advise me on how to get the right value?

Thanks
HSP
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
Are the numbers entered just number - not time
and
the output is a number for seconds and NOT a time function

=(INT(A1)*60)+(A1-TRUNC(A1))*100

will give seconds in number format

then
=((INT(A1)*60)+(A1-TRUNC(A1))*100)/86400
format the cells in [SS]

Book1
ABCDEFG
1A1-->0.3 --> 300.303030
2A2-->0.03 --> 030.030303
3A3-->1.3 --> 901.319090
4A4-->40.3 --> 243040.34024302430
Sheet1
Cell Formulas
RangeFormula
E1:E4E1=INT(D1)
F1:F4F1=(INT(D1)*60)+(D1-TRUNC(D1))*100
G1:G4G1=F1/86400
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Another option if you want a number & not time
=DOLLARDE(A1,60)*60
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,349
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top