Excel Question

[regulus]

Hi Folks,

Need a bit help here. Though it should be very simple. I just can't think at the moment

I want to calculate my Build Time my company spend on each building renovation we do. So I have two columns;

Start Date : Completion Date : Build Time (Days)
12/12/2014 24/04/2015

And upon entering the 'Completion Date' values, I want it to calculate the days it took for the completion, from start to finish and enter it in the 'Build Time' column, e.g, 133 days.

Any ideas?

 

[Sui]

Can't you just do =B2-A2?

On the assumption Start Date is in column A, compelted date in column B.

 

[Tomc@T]

Can't you just do =B2-A2?

On the assumption Start Date is in column A, compelted date in column B.

Yes exactly that.

 

[regulus]

ignore

 

[regulus]

One more question. Pic attached to help explain;



So I got it all working but need the cells H4 and I4 to be blank until I enter the Actual Completion Date in G4 which comes much later (pointed out by arrow). Can't be having these funky negative values.

How do I keep these cells empty until I enter the date in G4?

 

[Lartyconshayo]

Wrap it in an IF statement. =IF(G3="","",B2-A2)

Assuming G3 is your first cell then run that down the sheet.

 

[regulus]

Wrap it in an IF statement. =IF(G3="","",B2-A2)

Assuming G3 is your first cell then run that down the sheet.

Sorry, I don't quite understand that.

The only cells I have that does calculations are H3 and I3 in the table I linked

 

[RDM]

Sorry, I don't quite understand that.

The only cells I have that does calculations are H3 and I3 in the table I linked

The IF statement shown goes in H3 and will only do the calculation if there is data in cell G3.

 

[Lartyconshayo]

As rdm said put the if formula in the h3 and i3 cells. Then it will only show a value if g3 has a value. is is not blank.

 

[regulus]

As a forum elder, I'm allowed to be a noob one last time. I've simplified my columns but I still can't seem to get the above formula to work. Here's a picture



I want G3 to calculate the moment I enter the date on F3. But stay empty until I enter on F3. I've tried the code above (with correct columns) but still can't seem to have any luck.

If someone can show me exactly what to put where I can be rid of this one issue.

 

[VapourTrail]

What is the code that you have in G3?

 

[regulus]

What is the code that you have in G3?

=F3-E3

 

[413x]

Automatic calculate Missy be off

 

[VapourTrail]

Try putting this in G3
=IF(F3<>"",F3-E3,"")

 

[regulus]

I have no idea what you done there, all I know is, it works!

Thank you very much.

 

[VapourTrail]

No problem The IF(F3<>"" bit just checks to see if F3 is not empty. If it is, then it does the next step which is F3-E3 which does the calculation. If the cell is empty, it skips the calculation step and goes on to the ,"" bit which just leaves the cell blank.

 

[regulus]

Ignore, problem solved

 

[regulus]

Solved

 

[regulus]

Another quick question. Should be simple. Currently I'm keeping tabs on how long all my jobs are open. If it's over 30 days, I start counting it with this formula I hacked together from online sources;

=COUNTIF(Records!E3:E25,"<"&TODAY()-30)

So it literally does 1, 2, 3, etc. It just keep adding them. Now, I need to subtract them once I close the job down. For argument sake. I'm sitting at 12 jobs open that's been open for over 30 days. If I close one down, it should come down to 11. It doesn't. I can see I just have to add and IF statement somewhere in that formula. I just can't see how at the moment.

Any ideas?

 

[Grrrrr]

How do you "close down" a job? Is there a column that says "closed" or a job is deemed close if it has a sign off date or something else?