Formula

[Steved]

Hello from Steved

The formula below will convert 1105 to seconds ( which is
66300)
=((TRUNC(1105)*60)+(1105-TRUNC(1105))*100)

Below will convert 66300 seconds to 11.05
=IF(66300>72000,(66300-72000),66300)/6000
my isuue is that 1205 will convert to 0.05
I want the formula to convert it ot 12.05

any ideas please

Thanks

 

[Guest]

Hi Steved,
I'm not really sure what you are doing here, but the 1205 thing is because you are subtracting 72000. Change the formula to:

=66300/6000


Good Luck,
Mark Graesser
(e-mail address removed)
Boston MA

----- Steved wrote: -----

Hello from Steved

The formula below will convert 1105 to seconds ( which is
66300)
=((TRUNC(1105)*60)+(1105-TRUNC(1105))*100)

Below will convert 66300 seconds to 11.05
=IF(66300>72000,(66300-72000),66300)/6000
my isuue is that 1205 will convert to 0.05
I want the formula to convert it ot 12.05

any ideas please

Thanks

 

[Steved]

Thanks Mark for your quick reponse

Mark my objective is to change the seconds into firstly a
decimal time and as you rightfully point out 66300/6000
will give me 11.05 which is what I require. The second
issue is that I want it to be in a 12hr time hence
=IF(73200>72000,(73200-72000),73200)/6000 for the time
between 12.00pm to 11.59pm, without the -72000 in the
above formula 1305 would convert to 13.05, what I need is
1.05 which is fine, however when using 1220 as an example
which is 73200/6000 will give me 12.20 but the problem
lies in the above formula which returns 0.20 it only
happens between 1201 to 1259, 1300 to 1159 is okay.
any thoughts

cheers

-----Original Message-----
Hi Steved,
I'm not really sure what you are doing here, but the 1205

thing is because you are subtracting 72000. Change the
formula to:

 

[Guest]

Hi Steved
Try changing the first 72000 to 78000, also change the > to >=

=IF(A1>=78000,(A1-72000),A1)/600

I think this will give you what you want

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Steved wrote: ----

Thanks Mark for your quick repons

Mark my objective is to change the seconds into firstly a
decimal time and as you rightfully point out 66300/6000
will give me 11.05 which is what I require. The second
issue is that I want it to be in a 12hr time henc
=IF(73200>72000,(73200-72000),73200)/6000 for the time
between 12.00pm to 11.59pm, without the -72000 in the
above formula 1305 would convert to 13.05, what I need is
1.05 which is fine, however when using 1220 as an example
which is 73200/6000 will give me 12.20 but the problem
lies in the above formula which returns 0.20 it only
happens between 1201 to 1259, 1300 to 1159 is okay
any thought

cheer

-----Original Message----
Hi Steved
I'm not really sure what you are doing here, but the 1205

thing is because you are subtracting 72000. Change the
formula to

 

[Steved]

Mark yes

Thankyou very much for working through this formula
Have yourself a great day

Cheers

 

[Guest]

Happy to help

----- Steved wrote: ----

Mark ye

Thankyou very much for working through this formul
Have yourself a great da

Cheer