[Couriant]

I am playing an online web-based game and I'm trying to make a spreadsheet so I can keep track of what I have got.

One of the things I have trouble with is how show the build time in hh mm ss of the structures and units. The formula the game seems to be:

(example)

building costs 100 credits
the contruction will take 50 credits an hour
Time to build: 02:00:00

real example from game

building costs 292 credits
the contruction will take 55 credits an hour
Time to build: 5:18:33


Any idea on how to do this?

 

[The Villan]

You should be able to type into a cell 05:18:33 and press enter and it should automatically show it correctly.

If not, click on a blank cell, then select Format, Cells and then select the number Tab.

Under Category click on Custom
Then under type, scrolldown until you find the following format hh:mm:ss and then click on OK
When back in the cell you selected, type 05:18:33 and press enter

 

[Couriant]

I know how to do the time format but my question is from the value that is calculated, how do i show it in time format? (the calculation will save time )

Again from the first example, if the building costs 100 credits, and the construction time is 50 credits per hour, then the building will take 2 hours to build (which will show 02:00:00) or if the building cost is 50 credits, and the construction time is 100 credits, then the building will take 1/2 hour to build (00:30:00)

Well perhaps I'm thinking more of Stopwatch time than the normal time

 

[Jimmy the Hand]

Value 1 in date/time means a day. So 100/50 = 2 days. Divide it by 24 to get hours. Format the cell to show properly.

 

[Zack Barresse]

Example:

100 in A2
50 in B2
=(A2/B2)/24 in C2

50 in A3
100 in B3
=(A3/B3)/24 in C3 (or copied down from above.

Format column C as "[hh]:mm:ss" (w/o quotes)

Sample file is uploaded.

 

[Couriant]

I thought I replied to the thread... must of been a browser error

Jimmy's handy work made me realize what I was missing... the 24 to convert to the hours/mins/secs that I needed.

Thanks for you help guys! I will be sure to add you in the credits

 

[Zack Barresse]

Credits? Unless it's a good FPS or FF, count me out.

 

[Couriant]

psff.. fine

Also I was wondering... a couple of years back I was able to change the color of the text and fill of a cell by using formulas. Does anyone remember how to? Like for example, A1 is the cell changing color. The formula will be if A2 = 1 then the cell would be green, 2, cell is yellow, 3 cell is red.

 

[Zack Barresse]

Conditional Formatting, under the Format menu I believe. You only have 3 conditions though, unless you pre-format your cell to one specific (default) condition, then you have four. More than that requires VBA.

 

[Couriant]

I only need one

 

[Zack Barresse]

Then use the Formula Is portion. Be sure to note the active cell when you do this and the references you use, as they are related. So if you have A1 selected and your formula is "=A2=1", then you copy cell A1 to B1, then check the CF of B1, the formula should read "=B2=1". You can use absolute and relative in CF.

HTH