Tricky If statement involving dates and text - I'm stumped!!!

Hi all,

I'm trying to work out the depreciation amounts for future capital projects.
There are three scenarios: Users can either leave the Asset Capitalisation
Date blank, put in a date as from when the project will be finished and so we
can capitalise from then, or type in the amount of days they think the
project is going to take.

It's this third option that is driving me round the bend, and I don't
understand why they can't just put a finish date in, but that's just the
nature of our business. And to further complicate things, they don't just put
a number of days in, they write "240 Days" for example.

Anyway, on order to get the capitalisation date, I'm using the following
formula:

=IF(ISBLANK(A1),DefaultCapitalisationDate,IF(A1<=YearEndDate,A1,(LEFT(FIND("
",A1)+YearStartDate))))

This is fine for when no date, or a finish date is put in, but when they put
in a number of days, eg "150 Days", I cant get the formula to simply take the
number of days and add it to a predetermined date.

Long winded and complicated, I know, but if anybody has any thoughts please
let me know as this has been driving me nuts for the better part of a day!!!

Thanks a lot,

Mike

Looks to me as if you've left off the first argument of the LEFT function,
and rearranged some parens:
=IF(ISBLANK(A1),DefaultCapitalisationDate,IF(A1<=YearEndDate,A1,LEFT(A1,FIND(" ",A1))+YearStartDate))


"mike_vr" wrote:

> Hi all,
>
> I'm trying to work out the depreciation amounts for future capital projects.
> There are three scenarios: Users can either leave the Asset Capitalisation
> Date blank, put in a date as from when the project will be finished and so we
> can capitalise from then, or type in the amount of days they think the
> project is going to take.
>
> It's this third option that is driving me round the bend, and I don't
> understand why they can't just put a finish date in, but that's just the
> nature of our business. And to further complicate things, they don't just put
> a number of days in, they write "240 Days" for example.
>
> Anyway, on order to get the capitalisation date, I'm using the following
> formula:
>
> =IF(ISBLANK(A1),DefaultCapitalisationDate,IF(A1<=YearEndDate,A1,(LEFT(FIND("
> ",A1)+YearStartDate))))
>
> This is fine for when no date, or a finish date is put in, but when they put
> in a number of days, eg "150 Days", I cant get the formula to simply take the
> number of days and add it to a predetermined date.
>
> Long winded and complicated, I know, but if anybody has any thoughts please
> let me know as this has been driving me nuts for the better part of a day!!!
>
> Thanks a lot,
>
> Mike

Sloppy mistakes that cost me half a day - nice!!!

Thanks for the help, that's working now, really appreciate it!

Mike

"bpeltzer" wrote:

> Looks to me as if you've left off the first argument of the LEFT function,
> and rearranged some parens:
> =IF(ISBLANK(A1),DefaultCapitalisationDate,IF(A1<=YearEndDate,A1,LEFT(A1,FIND(" ",A1))+YearStartDate))
>
>
> "mike_vr" wrote:
>
> > Hi all,
> >
> > I'm trying to work out the depreciation amounts for future capital projects.
> > There are three scenarios: Users can either leave the Asset Capitalisation
> > Date blank, put in a date as from when the project will be finished and so we
> > can capitalise from then, or type in the amount of days they think the
> > project is going to take.
> >
> > It's this third option that is driving me round the bend, and I don't
> > understand why they can't just put a finish date in, but that's just the
> > nature of our business. And to further complicate things, they don't just put
> > a number of days in, they write "240 Days" for example.
> >
> > Anyway, on order to get the capitalisation date, I'm using the following
> > formula:
> >
> > =IF(ISBLANK(A1),DefaultCapitalisationDate,IF(A1<=YearEndDate,A1,(LEFT(FIND("
> > ",A1)+YearStartDate))))
> >
> > This is fine for when no date, or a finish date is put in, but when they put
> > in a number of days, eg "150 Days", I cant get the formula to simply take the
> > number of days and add it to a predetermined date.
> >
> > Long winded and complicated, I know, but if anybody has any thoughts please
> > let me know as this has been driving me nuts for the better part of a day!!!
> >
> > Thanks a lot,
> >
> > Mike