Ken Johnson Wrote:
Hi Craig,
Firstly, I have noticed a problem with dates. Excel always interprets
the cell input 12/12/06 to mean "December 12, 2006". By that I mean
Excel will always take the first part to be the month, second part the
day and last part the year. You can format the cells to DISPLAY the
date in a different order, however, that format does not affect the
required order when the date is typed into the cell. So, if the cel
is
formatted as Date and Locale (location): = "English (Australia)" (I
live in Sydney Australia, where we put the day before the month) an
if
I type 6/12/06, thinking it means December 6 2006, Excel still
interprets this to be June 12 2006 and the cell will display 12/06/06.
How's that for confusing!
Also, when you enter 15/12/06 you are obviously meaning the Decembe
15
2006, but Excel couldn't give a damn and refuses to convert it to a
date and treats it as text, so it ends up aligned on the left side of
the cell like all other text inputs.
The simplest solution to this day/month confusion is to format all
cells with dates using one of the unambiguous options such as
14-Mar-06, which you should see in the list of date format types when
you go Format|Cells|Number tab|Date.
Try this formula in AX2, filled down as far as required...
=IF(UPPER(AJ2)="N/A",AV2,IF(UPPER(AV2)="N/A",AJ2,IF(ISNUMBER(AV2),MAX(AJ2,AV2),"")))
I've used the UPPER function so that you don't have to worry abou
case
sensitivity with the "N/A" inputs, you will get the same result with
"N/A", "n/a", "n/A" and "N/a"
Give it a thorough testing and let me know how it goes.
Ken Johnson
Hi Ken,;)
Thanks for getting back to me, i have given this a run today and worke
-*_'ok'__*-but not quite what i wish for.
Why it worked '_-*ok'*-_? In column aj2 (internals jobs which ar
kitchen works, bathroom, rewire etc) and av 2(externals jobs which ar
roof works and others), and in column ax is to stated the propery i
overall completed.
The reason we set up internals and externals was mainly for on how man
internals and external jobs was completed in month march, april etc.
So with the function that you have give and i tried it out, and if
leave the internals job as a 'blank' which to stating that the work i
to start or in progress and the externals jobs are completed, th
column ax will show the date of when the external jobs,so bascially th
property is not completed.
-(It will not make sense to the other part of programme that i have se
it up and this will confuse other members of staff who will have acces
to this programme.)-
Is there any other way that if we input the date for the external job
as completed but leave it blank for the internal job and in the colum
ax remind 'blank' unless the internal job is marked as 'N/A', the sam
if it was other way round if the internal jobs completed and externa
jobs 'blank' unless marked 'N/A'.....it seem to me that i'm going o
and on and on.......*but* the more information that i give to you, th
better you understand what i'm after.
Anyway, thanks for your help again, (i wish i was in Australia, if
can only afford the flight!)
Craig:
--
craig@hel
-----------------------------------------------------------------------
craig@help's Profile:
http://www.excelforum.com/member.php...fo&userid=3620
View this thread:
http://www.excelforum.com/showthread.php?threadid=56316