Microsoft excel

On the microsoft exel spereadsheet thing can you make the decimals count in increments of 60 instead of 100? Instead of .3+.3=.6 /// .3+.3 would equal 1

 

I've never tried, but, I think IF you tweak the settings you could do it...

 

Are you trying to do time calcuations? If so there is a better way...I have to rememebr it though first

 

I've seen something where I work where they use a spread sheet to add up all the various times for different things. They put in all the times in their proper rows (left to right) and then at the end it adds them all together, but it does it based on 100 not 60. I think this is what J4E was talking about.

 

Juggs is banned at this point so I don't think any of this matters any longer.

 

I belive that even if he's banned he can still come on and read posts.

 

Theres a few solutions...it depends on how you are entering the values in the data cells...


If you want to use xx:xx:xx format the cellls for time and they will add up.

What format will the values be entered be expressed in (e.g. 1.11, 1.78, 1:15) and what format should the result be in?

 

like in the example above is the .3 being input 3/10'ths of something, or is it 3 mins, or is it 3 seconds?

 

Jug can still READ the posts. OR, if need be, He's one of MY Admins. I can relay the message. I'm pretty sure, Excell can do TIME calculations. (I don't use it much. YET! If plans come together, I will!)

 

Just wondering why Juggs would get banned. Seemed harmless.

 

1.25 should equal 1 hour and 25 five minutes so 1.25 + 2.35 = 4.00

 

im going to assuem that you don't have the analysis pack installed (if so there are some good function in it that could help)

first suggestion..change your inputs! The best way for excel to handle them would be to sue actual numbers e.g 1.5 equals 1 1/2 hours (90mins) the next best would be to format the cels {[hh]:mm} and enter the times as 1:30 for 1 hour 30 mins...

but assuming you cant do that..and all you want to do is add the row across you can try this (its quick and dirty because im high right now!) drawback is t hat is uses 'helper cells'

Lets assume you've got some values in a1:d1

Code:

 
[font=Arial]3.59--[/font][font=Arial]2.59--[/font][font=Arial]3.59--[/font][font=Arial]7.59[/font]

and you want to output the value in to e1

f1 and g1 are going to be helper cells, make them invisible

f1 will add all the mins portions together and express as an integer:
=((A1-INT(A1))*100)+((B1-INT(B1))*100)+((C1-INT(C1))*100)+((D1-INT(D1))*100)

g1 will find out how many mins of f1 are convertable in to hours and store the 'remainder' as a decimal notation:
=(MOD(F1,60))/100

e1 is your solution:
=((F1-(G1*100))/60)+(INT(A1)+INT(B1)+INT(C1)+INT(D1))+G1


this will leave your output in the same format that you started with :
cell results are:

a1:g1
3:59 2:59 3:59 7:59 18:56 236 0.56

the downside is that if you need to work with the numbers again you will have to go threw the mess again (e.g. if you want columar totals of the row totals or want to preform math functions such as billing per time calcutated or overtime forumals)

Hope this helps...maybe someone can tighen up my code some too...i proablly wont want to look at it again when im sober

 

I'm gonna have someone else look at this cause i only understand half of it. it's ot to big of a problem cause I can add it all up in my head but the new guy can't so he's using the old, very old, spreadsheet thing they use to use and whenever I try to explain to him why it doesn't work, his eyes cross and shit comes out of his ears.

 

I got it, thanks for all the help

 

yea that thing is giving me a headache now, not sure how i was able to concentrate on it last night!!! like i said it may be a little bloated code =)


Btw welcome back