=if(h1800;e180-$j$1;0)

[paulrm906]

=IF(H1800;E180-$J$1;0)

H180 = empty cell with a formula in it.
E180 = the date a person was interviewed.
$J$1 = =()NOW

In the above formula H180 is an empty cell with a formula in it (which
will later have their actual starting date in it) and E180 is the date
a new staff member should commence work and J1 is equal to =()now. So
now I want to write a formula simular to the above formula or another
formula that does the job in cell J180 and what I want it to do in
cell
J180 is tell me how many days until their starting date. and to do
this
I need to subtract E180 (starting date) from J1 (=()now. plus I only
want an answer in cell J180 if they have not started and once I enter
the date in E180 then I just want it left blank. I hope I have not
confused everyone with this as I got totally confused writing it.

Thanks

Paul Maynard

 

[CLR]

Perhaps this will do as you wish..........

=IF(H180="",TEXT(E180-NOW(),"#")&" days","")

Vaya con Dios,
Chuck, CABGx3

 

[paulrm906]

Thanks Chuck

For your suggestion for my formula and yes it works great but
unfortunately it will not work when I enter a formula in H180 even if
there is no result. If you have any further suggestions with the
formula with getting it to work with a formula in H180 I would
appreciate it very much as I have been trying to work this one out now
for some now.

Regards

Paul Maynard
Moscow
Russia

 

[CLR]

Hi Paul..........

My original formula anticipated that if H180 did not contain a date, that
the result of the formula would be a null, (ie: ""). That formula will not
work if the result is a zero, or any other character(s). If the result is a
zero or a null, try this version.......

=IF(OR(H180=0,H180=""),TEXT(E180-NOW(),"#")&" days","")

If it is actually something else, like the text, "Not Started Yet", then
the formula can be modified accordingly...........

Vaya con Dios,
Chuck, CABGx3

 

[paulrm906]

Hello Chuck

Again your formula works great if I delete the formula H180, I have
tried all differant alternatives myself but still it will not work.
But thanks for trying.

Paul Maynard
Moscow
Russia.

 

[CLR]

Well Paul, I don't know what else to do unless you can tell me exactly what
formula it is you are using in H180. Perhaps it is giving a space as a
result if no date is returned, or something else that we must accomodate in
the other formula....I'm sure that's all it is, but I must see the formula
in order to help further.......

Vaya con Dios,
Chuck, CABGx3

 

[paulrm906]

Thanks Chuck

Below is the formula that is in H180, this formula checks the page
where the new staff are entered and feeds the date across to H180 after
their starting date as been entered on the sheet called "New Staff". And
below is the exact formula that is in H180. Hope this helps and thanks
again for your persistance in trying to help me.

=INDEX('New Staff'!$A$3:$M$1000;MATCH($A180;'New
Staff'!$B$3:$B$1000;0);6)

Thanks

Paul Maynard
Moscow
Russia.

 

[CLR]

Hi Paul....
The problem/solution lies not the fact of whether or not there is a formula
in H180, but of what result is is leaving for the new formula in J180 to
deal with.........without having all your relative data and files, I got
#N/A in H180.....so this version of the new formula will solve for that
result....if this does not do it for you, then tell me exactly what result
you are getting in cell H180 so we can fix it.

=IF(ISNA(H180),TEXT(E180-NOW(),"#")&" days","")

BTW, I trust you "are" converting for the differences between our Excel
versions by changing my commas to your semi-colons....right?

Vaya con Dios,
Chuck, CABGx3

 

[paulrm906]

Thanks very much Chuck it works very good and thanks again for your
persistance in trying to help me solve this formula.

Paul Maynard
Moscow
Russia.

 

[CLR]

You're welcome Paul.......glad you got it working, and thanks for the
feedback.....

Vaya con Dios,
Chuck, CABGx3