Excel wizard needed

[Scaley brat]

I am trying to add 2.30 to 2.45 in excel and need the answer to read 5.15.
I need it to treat everything after the . as minutes NOT decimal, is there anyone who can help me make this add correctly please.

It's doing my bloody swede in now, I've been looking at it all day DT_Xtremez_37:


PLEASE GOD HELP ME !!!! DT_Xtremez_41:

 

[propersplitbrainme]

Try this link...

http://www.contextures.com/xlfaqDat.html#AddTime

Essentially you need to insert the data as 'time' and not as decimal numeric values.

 

[Weebl]

Easy way, format the cells as 'time'

When you enter the date use ':' instead of '.' as your serparator and you will have to write the second zero in '30' so 2:30 instead of 2.3

Either way though, 2:30 plus 2:45 will not add up to 6:15

 

[chiefy]

Either way though, 2:30 plus 2:45 will not add up to 6:15

Beat me to it DT_Xtremez_28: but it may have been fun to let him look at it all night too!

 

[monobrow]

Lits god and can't do decimal time???

FFS!!!

2:30 becomes 2.5
2:45 becomes 2.75

add them together to get 5.25 = 5:15

Could do this though....

Adding Times

You can add times using the =SUM worksheet function. Just enter all of your times as HH:MM:SS, and then
use SUM to add them up. You may leave off the :SS if you prefer. By default, Excel will display the sum of
times in "time-of-day" format, meaning that adding 12:30 + 12:45 will yield 01:15. You can prevent Excel
from "rolling over" at 24 hours by formatting the result cell as [h]:mm which will cause it to display 25:15
rather than 01:15.

If you want to add up minutes and seconds, you must include a leading "0:" in your data. For example,
enter "0:10:20" to indicate 10 minutes, 20 seconds. When you sum these times, Excel will display the
sum in "time-of-day" format, meaning that adding 0:40:10 and 0:30:20 will yield 1:10:30. You can prevent
Excel form "rolling over" at the hour by formatting the result cell as [m]:ss which will cause it to
display 70:30 rather than 1:10:30.

Another method of adding times is to use the TIME function. To add 1 hour, 35 minutes, 10 seconds to a time in A1, use the function

=A1 + TIME(1,35,10)

From http://www.cpearson.com/excel/datearith.htm

 

[Scaley brat]

Oh bugger :: DT_Xtremez_42: 5:15..... you know what I meant ! ::/:

The report exported it as 2.30. all the time values are correct, but they have the decimal point instead of the colon. I need the manhours off about 4000 cards and would rather not sit there manually adding them all up with my obviously dyslexic fcuking maths skills DT_Xtremez_15:

Mono, change your underwear and fcuk off !! :: DT_Xtremez_31:

 

[Past Engineering]

Blast, getting to old for this game, I was just working out how to tell him the answer (the correct answer to boot), but Weebl & monobrow beat me to it.

 

[Weebl]

The report exported it as 2.30. all the time values are correct, but they have the decimal point instead of the colon.

You can use find and replace to change the decimals to colons.

Still going to end up with 2:03 though instead of 2:30.

 

[monobrow]

Mate, deciaml time conversion chit in the .zip file.... Add it all up in decimal on calculator then read off the minuites back off it. Never fails!

Godsend for LITS stuff ::

 

[Scaley brat]

I think basically I need it to add everything after the decimal point and roll it over when it hits 60 then add that figure to the sum of the figures before the decimal point...... bloody IQ reports trying to be helpful have knackered it for me !!

You can't do CTRL H to replace the '.' with ':' that then alters the figure. Formatting the column to hours alters it as well

2.30 2:30
3.30 3:30
2.40 2:40
0.10 0:10
8.30 8:30
0.20 0:20
1.30 1:30
2.00 2: 00
19.90 21:10

As you can see there is a lot of room for error here

 

[monobrow]

I think basically I need it to add everything after the decimal point and roll it over when it hits 60 then add that figure to the sum of the figures before the decimal point...... bloody IQ reports trying to be helpful have knackered it for me !!

You can't do CTRL H to replace the '.' with ':' that then alters the figure. Formatting the column to hours alters it as well

2.30 2:30 2.5
3.30 3:30 3.5
2.40 2:40 2.666
0.10 0:10 0.166
8.30 8:30 8.5
0.20 0:20 0.333
1.30 1:30 1.5
2.00 2: 00 2.0
19.90 21:10 21.166 read off..... 21:10

As you can see there is a lot of room for error here

Job done, use a calculator!!!

 

[PingDit]

Want the easy way?...

Firstly, as has already been said, format, cells, time (take the top option of 13:30)
In cell A1, write Hours/Mins (This is just to give it a title)
In cell A2, enter 2:30
In cell A3 enter 2:45
Put the cursor in A4 and highlight it.
In the formulae bar at the top, enter;
=Sum(A2:A3)
Hit enter......Bingo
Your answer of 5:10 should now appear in cell A4.

Cheers,

PingDit

 

[Weebl]

You can't do CTRL H to replace the '.' with ':' that then alters the figure. Formatting the column to hours alters it as well

It alters the figure if it is still a decimal one, you need to find and replace to colons first with no formatting on the cell, then after it has done that (it will have changed everything to 2:03 and 3:04 though) then you can format the cells as time.

Still have to go through and add all the zeros back in though from what I can see?

 

[Scaley brat]

Job done, use a calculator!!!

4000 cards, multiple trades........ Well I think my calendar is empty for the next 2 years :S

I'll have to manually enter each value in a new column (E.g 2.30 as 2:30) to get them into analogue format than autosum it by trade then add the trade totals........... by tomorrow. DT_Xtremez_17:

 

[Weebl]

Want the easy way?...

Firstly, as has already been said, format, cells, time (take the top option of 13:30)
In cell A1, write Hours/Mins (This is just to give it a title)
In cell A2, enter 2:30
In cell A3 enter 2:45
Put the cursor in A4 and highlight it.
In the formulae bar at the top, enter;
=Sum(A2:A3)
Hit enter......Bingo
Your answer of 5:10 should now appear in cell A4.

Cheers,

PingDit

Um, yea, that is all easy and we already have that, however he is trying to work off a set of figures that have been exported and wants a way of doing it without manual data entry.

 

[Weebl]

Aha.

Copy all your exported data.

Paste it into notepad.

Find and replace the decimals with the colons.

Format the excel document as time.

Paste the notepad data into the excel spreadsheet.

Add 'em up.

Does that work?

 

[Scaley brat]

Aha.

Copy all your exported data.

Paste it into notepad.

Find and replace the decimals with the colons.

Format the excel document as time.

Paste the notepad data into the excel spreadsheet.

Add 'em up.

Does that work?

I have made a copy and done a page manually..... it sux the devils *** on fire ::/: I will try the note pad bit and be right back !

 

[Scaley brat]

It is a bit quicker, you have to add ':00' to all single rounded hours, and a 0 to all ten values (10, 20, 30, 40, 50) but it is a bit quicker. Do you realise how many bloody people put 0:10 for an entry ! DT_Xtremez_41:

Top favourites so far.....
0:10
0:30
1:00

Buggeration ! DT_Xtremez_31:

 

[Weebl]

It is a bit quicker, you have to add ':00' to all single rounded hours, and a 0 to all ten values (10, 20, 30, 40, 50) but it is a bit quicker. Do you realise how many bloody people put 0:10 for an entry ! DT_Xtremez_41:

Top favourites so far.....
0:10
0:30
1:00

Buggeration ! DT_Xtremez_31:

Well can you paste it as is into an unformatted excel spreadsheet, format all as a number with 2 decimal places, this should add all the zeros for you. then copy off to notepad as before.

 

[theladf]

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)