Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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<=Y earEndDate,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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky If statement involving dates and text - I'm stumped!!!
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<=Y earEndDate,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<=Y earEndDate,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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky If statement involving dates and text - I'm stumped!!!
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<=Y earEndDate,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<=Y earEndDate,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates formatted as text | Excel Discussion (Misc queries) | |||
dates and text | Excel Discussion (Misc queries) | |||
text to dates? | Excel Worksheet Functions | |||
Text with Dates | Excel Discussion (Misc queries) | |||
COUNTIF With Multiple Dates, Columns and Text | Excel Worksheet Functions |