Excel wizard needed

[Scaley brat]

SB your cred as computer master and LITs guru is in tatters!DT_Xtremez_17: I will send Baz (the Master) across to give you some assistanceDT_Xtremez_31: (PS I'm on my bike soon so Baz is all yours)

Don't you dare, I'll hunt you down and pond you..... LITS I can do, it's this MS bollox has me stumped !

I started with this:
=INT(A1)+(MOD(A1,1)*60/100) and also tried this
=INT(A1)+(MOD(A1,1)*100/60) but they're not playing ! DT_Xtremez_42:
Bloody Microtw@ts, it's a conspiracy ! DT_Xtremez_25:

 

[Weebl]

Have you tried getting excel to add the zeros for you using format/number/2 decimal places before cutting and pasting to notepad?

 

[Scaley brat]

Have you tried getting excel to add the zeros for you using format/number/2 decimal places before cutting and pasting to notepad?

Changed the values mate.


Thank you everyone who tried to help, I am off home soon, 13 1/2 hours is enough for me. It's dark outside ! DT_Xtremez_17:

 

[Albert Park]

If your souce data is displayed as follows

2.30
2.45 etc

then do this:

replace all the '.' with ':'

open Excel

format a column as Number

From Data menu

Select-Import External Data-Import Data

Navigate to file containing data

Follow the wizard and choose a cell where you want to start entering data (the column you formatted as Number)

After the data has imported format the column as follows;

From Format menu

Select Cells-Custom

Scroll down until you see hh:mm and select it

Finally go to the bottom of you data and select the Autosum icon (like a sideways M on toolbar)

Voila.

 

[Weebl]

Changed the values mate.


Thank you everyone who tried to help, I am off home soon, 13 1/2 hours is enough for me. It's dark outside ! DT_Xtremez_17:

Not if you paste to an unformatted spreadsheet and then copy off after adding the zeros.

Do you want to sent the figures to me? I have had a play with dummy numbers to work out the best way to help you and have got it to work this end.

 

[ReluctantClerk]

SB

I tried some figs with one of your formulas and it works.

If you enter 4.25 in cell A1 then, your formula =INT(A1)+(MOD(A1,1)*100/60) in cell b1 it gives you the decimal i.e. 4.416666666 etc format to number 2 dec points. Drag formula down all adjacent cells to your original figures, below the last figure in Col B add all of Col B using the =SUM formula.

5 mins = 0.08
10 = 0.17
15= 0.25
20 = 0.33
25 = 0.42
30 = 0.50
35 = 0.58
40 = 0.67
45 = 0.75
50 = 0.84
55 = 0.92

The last bit isn't meant for egg sucking but obviously your total will be decimal not minutes - ooh takes me back to my Stats days this!!!!!!
DT_Xtremez_28:

 

[Scaley brat]

THanks for the help guys.
THe problem was
2 hours 45 minutes was exported as 2.45 not 2:45. All went well until we realised that they weren't decimal values, meaning we had lost a large amount of time. Thasnks for the offer Weebl, I missed that before logging out last night !


Nearly done. I used the auto filter on the time values. Change value in first box, copy into the others. It saved a load of time...