Formula Problems.... if then else?

[Larry R]

I have 2 calculated fields in my spreadsheet. Column F is the contract
start date, column G is the end date. The calculated fields are Column H
that tells me how many days are in the contract and column J that tells me
how many contract days has past since the contract start date.



I wanted all calculated cells in rows not used to be null. Pete_UK was able
to quickly help me on that. But another problem developed today that I can
not overcome, the contracts not started yet show the amount of days between
the contract start date and today() date in the column H and shows the
amount of days between contract end date in column J.



I need H and J to be null too, until the contract has reached the start
date. Below is what I am using at this time:





Column H: =IF(OR(G5="",F5=""),"",G5-F5+1)



Column J: =IF(G5="","",G5-TODAY())



Thanks in advance for any help,

LarryR

 

[clare.moe]

I have 2 calculated fields in my spreadsheet.  Column F is the contract
start date, column G is the end date.  The calculated fields are Column H
that tells me how many days are in the contract and column J that tells me
how many contract days has past since the contract start date.

I need H and J to be null too, until the contract has reached the start
date.  Below is what I am using at this time:

Column H:  =IF(OR(G5="",F5=""),"",G5-F5+1)

Column J:  =IF(G5="","",G5-TODAY())

Hmm .. looks like col. J is trying to give contract days remaining,
not days passed?

So: I added column I for days past and left column J for days
remaining. If the contract has not started yet days in contract will
calculate, days passed will show zero (0) and days remaining will show
null. Also, if the contract has concluded days remaining will show as
zero (0).

Column H: =IF(OR(G5="",F5=""),"",G5-F5+1)

Column I: =IF(H5="","",IF(F5>TODAY(),0,MIN(G5-F5+1,TODAY()-F5)))

Column J: =IF(OR(H5="",F5>TODAY()),"",IF(TODAY()>G5,0,G5-TODAY()))

Have fun!

CM