Macro start and finish date from rows
 

is there a macro that will put the start date and finish date for each of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

Macro start and finish date from rows
 

Your question is not entirely clear (which may be due to the way my
newsreader has formatted your example data)... can you provide a little more
information about what you have, where you have it and what you want it to
look like afterwards?

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
is there a macro that will put the start date and finish date for each of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

Macro start and finish date from rows
 

Thank you Rick

Basically what I need to do is:

Our people plan their work in excel by putting a task and then allocating
hours in each month they will be working. Sometimes they skip a month or two
and then restart. I need to look at each row and see the first month the
hours appears in and put this a start date. Then I need to look at which
month their hours end and put this as the finish date. The only way I know
how to do it is manually. The end result is to do a quick view in MS project
gantt.
Hope this is clear.

"Rick Rothstein" wrote:

Your question is not entirely clear (which may be due to the way my
newsreader has formatted your example data)... can you provide a little more
information about what you have, where you have it and what you want it to
look like afterwards?

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
is there a macro that will put the start date and finish date for each of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

Macro start and finish date from rows
 

Use these array-entered** formulas where indicated and then copy them
down...

B2: =INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))
C2: =INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

**commit both formulas using Ctrl+Shift+Enter, not just Enter by itself

Change the last column from the Z's that I used to your actual last date
column.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Thank you Rick

Basically what I need to do is:

Our people plan their work in excel by putting a task and then allocating
hours in each month they will be working. Sometimes they skip a month or
two
and then restart. I need to look at each row and see the first month the
hours appears in and put this a start date. Then I need to look at which
month their hours end and put this as the finish date. The only way I know
how to do it is manually. The end result is to do a quick view in MS
project
gantt.
Hope this is clear.

"Rick Rothstein" wrote:

Your question is not entirely clear (which may be due to the way my
newsreader has formatted your example data)... can you provide a little
more
information about what you have, where you have it and what you want it
to
look like afterwards?

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
is there a macro that will put the start date and finish date for each
of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

Macro start and finish date from rows
 

Hi Rick, I am sorry I don't understand. Do I copy your formula into columns
b2 and c2?

"Rick Rothstein" wrote:

Use these array-entered** formulas where indicated and then copy them
down...

B2: =INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))
C2: =INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

**commit both formulas using Ctrl+Shift+Enter, not just Enter by itself

Change the last column from the Z's that I used to your actual last date
column.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Thank you Rick

Basically what I need to do is:

Our people plan their work in excel by putting a task and then allocating
hours in each month they will be working. Sometimes they skip a month or
two
and then restart. I need to look at each row and see the first month the
hours appears in and put this a start date. Then I need to look at which
month their hours end and put this as the finish date. The only way I know
how to do it is manually. The end result is to do a quick view in MS
project
gantt.
Hope this is clear.

"Rick Rothstein" wrote:

Your question is not entirely clear (which may be due to the way my
newsreader has formatted your example data)... can you provide a little
more
information about what you have, where you have it and what you want it
to
look like afterwards?

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
is there a macro that will put the start date and finish date for each
of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

Macro start and finish date from rows
 

Select B2 and then copy/paste this formula into the **Formula bar** at the
top of the grid...

=INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))

and then (and this is important), press Ctrl+Shift+Enter to 'enter' it. Then
select C2 and do the identical procedure with this formula...

=INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

Now select both B2 and C2, then hover the cursor over the little black
square in the bottom right corner of the selection. When the cursor turns to
a small black "cross", click and drag the selection down to the row where
the last activity is located, then release the mouse button. Your start and
end dates should now fill in.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Hi Rick, I am sorry I don't understand. Do I copy your formula into
columns
b2 and c2?

"Rick Rothstein" wrote:

Use these array-entered** formulas where indicated and then copy them
down...

B2: =INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))
C2: =INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

**commit both formulas using Ctrl+Shift+Enter, not just Enter by itself

Change the last column from the Z's that I used to your actual last date
column.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Thank you Rick

Basically what I need to do is:

Our people plan their work in excel by putting a task and then
allocating
hours in each month they will be working. Sometimes they skip a month
or
two
and then restart. I need to look at each row and see the first month
the
hours appears in and put this a start date. Then I need to look at
which
month their hours end and put this as the finish date. The only way I
know
how to do it is manually. The end result is to do a quick view in MS
project
gantt.
Hope this is clear.

"Rick Rothstein" wrote:

Your question is not entirely clear (which may be due to the way my
newsreader has formatted your example data)... can you provide a
little
more
information about what you have, where you have it and what you want
it
to
look like afterwards?

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
is there a macro that will put the start date and finish date for
each
of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

Macro start and finish date from rows
 

FABULOUS THANK YOU IT WORKS PERFECTLY! /Mary Lou

"Rick Rothstein" wrote:

Select B2 and then copy/paste this formula into the **Formula bar** at the
top of the grid...

=INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))

and then (and this is important), press Ctrl+Shift+Enter to 'enter' it. Then
select C2 and do the identical procedure with this formula...

=INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

Now select both B2 and C2, then hover the cursor over the little black
square in the bottom right corner of the selection. When the cursor turns to
a small black "cross", click and drag the selection down to the row where
the last activity is located, then release the mouse button. Your start and
end dates should now fill in.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Hi Rick, I am sorry I don't understand. Do I copy your formula into
columns
b2 and c2?

"Rick Rothstein" wrote:

Use these array-entered** formulas where indicated and then copy them
down...

B2: =INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))
C2: =INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

**commit both formulas using Ctrl+Shift+Enter, not just Enter by itself

Change the last column from the Z's that I used to your actual last date
column.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Thank you Rick

Basically what I need to do is:

Our people plan their work in excel by putting a task and then
allocating
hours in each month they will be working. Sometimes they skip a month
or
two
and then restart. I need to look at each row and see the first month
the
hours appears in and put this a start date. Then I need to look at
which
month their hours end and put this as the finish date. The only way I
know
how to do it is manually. The end result is to do a quick view in MS
project
gantt.
Hope this is clear.

"Rick Rothstein" wrote:

Your question is not entirely clear (which may be due to the way my
newsreader has formatted your example data)... can you provide a
little
more
information about what you have, where you have it and what you want
it
to
look like afterwards?

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
is there a macro that will put the start date and finish date for
each
of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

Macro start and finish date from rows
 

You are quite welcome. Just checking to be sure... you did remember to
change the Column Z reference I used in my example to a column designation
that would cover all your possible data, right? Also, a side note about
array-entered formulas... if you ever have to modify one in the future, you
must recommit the formula using Ctrl+Shift+Enter and never just Enter by
itself.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
FABULOUS THANK YOU IT WORKS PERFECTLY! /Mary Lou

"Rick Rothstein" wrote:

Select B2 and then copy/paste this formula into the **Formula bar** at
the
top of the grid...

=INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))

and then (and this is important), press Ctrl+Shift+Enter to 'enter' it.
Then
select C2 and do the identical procedure with this formula...

=INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

Now select both B2 and C2, then hover the cursor over the little black
square in the bottom right corner of the selection. When the cursor turns
to
a small black "cross", click and drag the selection down to the row where
the last activity is located, then release the mouse button. Your start
and
end dates should now fill in.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Hi Rick, I am sorry I don't understand. Do I copy your formula into
columns
b2 and c2?

"Rick Rothstein" wrote:

Use these array-entered** formulas where indicated and then copy them
down...

B2: =INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))
C2: =INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

**commit both formulas using Ctrl+Shift+Enter, not just Enter by
itself

Change the last column from the Z's that I used to your actual last
date
column.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Thank you Rick

Basically what I need to do is:

Our people plan their work in excel by putting a task and then
allocating
hours in each month they will be working. Sometimes they skip a
month
or
two
and then restart. I need to look at each row and see the first month
the
hours appears in and put this a start date. Then I need to look at
which
month their hours end and put this as the finish date. The only way
I
know
how to do it is manually. The end result is to do a quick view in MS
project
gantt.
Hope this is clear.

"Rick Rothstein" wrote:

Your question is not entirely clear (which may be due to the way my
newsreader has formatted your example data)... can you provide a
little
more
information about what you have, where you have it and what you
want
it
to
look like afterwards?

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
is there a macro that will put the start date and finish date for
each
of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

Macro start and finish date from rows
 

Good afternoon (Montreal time)

Thank you again; I understand column Z reference. I did not know who to do
this type of fomula. You explained exactly how I need to do this. It is great
- passed it on to my colleagues here. Some of them are programmers but did
not know this function. Really appreciate the help.

Now I need to learn how to use this discussion group - cannot seem to sort
in date order so I could not find your reply till now.

/Mary Lou

"Rick Rothstein" wrote:

You are quite welcome. Just checking to be sure... you did remember to
change the Column Z reference I used in my example to a column designation
that would cover all your possible data, right? Also, a side note about
array-entered formulas... if you ever have to modify one in the future, you
must recommit the formula using Ctrl+Shift+Enter and never just Enter by
itself.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
FABULOUS THANK YOU IT WORKS PERFECTLY! /Mary Lou

"Rick Rothstein" wrote:

Select B2 and then copy/paste this formula into the **Formula bar** at
the
top of the grid...

=INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))

and then (and this is important), press Ctrl+Shift+Enter to 'enter' it.
Then
select C2 and do the identical procedure with this formula...

=INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

Now select both B2 and C2, then hover the cursor over the little black
square in the bottom right corner of the selection. When the cursor turns
to
a small black "cross", click and drag the selection down to the row where
the last activity is located, then release the mouse button. Your start
and
end dates should now fill in.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Hi Rick, I am sorry I don't understand. Do I copy your formula into
columns
b2 and c2?

"Rick Rothstein" wrote:

Use these array-entered** formulas where indicated and then copy them
down...

B2: =INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))
C2: =INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

**commit both formulas using Ctrl+Shift+Enter, not just Enter by
itself

Change the last column from the Z's that I used to your actual last
date
column.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Thank you Rick

Basically what I need to do is:

Our people plan their work in excel by putting a task and then
allocating
hours in each month they will be working. Sometimes they skip a
month
or
two
and then restart. I need to look at each row and see the first month
the
hours appears in and put this a start date. Then I need to look at
which
month their hours end and put this as the finish date. The only way
I
know
how to do it is manually. The end result is to do a quick view in MS
project
gantt.
Hope this is clear.

"Rick Rothstein" wrote:

Your question is not entirely clear (which may be due to the way my
newsreader has formatted your example data)... can you provide a
little
more
information about what you have, where you have it and what you
want
it
to
look like afterwards?

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
is there a macro that will put the start date and finish date for
each
of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

Macro start and finish date from rows
 

You should consider using a news reader like Outlook Express or Window Mail
(which is Vista's version of Outlook Express)... the interface is much
easier to use (sorting by date, grouping threads, highlighting only your
threads, etc.). To do this, you would create an account
(Tools/Accounts/Add/Newsgroup) for the Microsoft public server. Use
msnews.microsoft.com as the server name and I would suggest you *not* use
your real email address in order to avoid getting on spam lists. Once your
account is set up, you can subscribe to whatever newsgroups you want (use
the search box the subscribe dialog provides to narrow the huge list down to
the groups you want... for example, type excel in the search box). After
doing this, the newsgroup will appear in a listing along with your email
accounts and you interact with it in virtually the same way.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Good afternoon (Montreal time)

Thank you again; I understand column Z reference. I did not know who to do
this type of fomula. You explained exactly how I need to do this. It is
great
- passed it on to my colleagues here. Some of them are programmers but did
not know this function. Really appreciate the help.

Now I need to learn how to use this discussion group - cannot seem to sort
in date order so I could not find your reply till now.

/Mary Lou

"Rick Rothstein" wrote:

You are quite welcome. Just checking to be sure... you did remember to
change the Column Z reference I used in my example to a column
designation
that would cover all your possible data, right? Also, a side note about
array-entered formulas... if you ever have to modify one in the future,
you
must recommit the formula using Ctrl+Shift+Enter and never just Enter by
itself.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
FABULOUS THANK YOU IT WORKS PERFECTLY! /Mary Lou

"Rick Rothstein" wrote:

Select B2 and then copy/paste this formula into the **Formula bar** at
the
top of the grid...

=INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))

and then (and this is important), press Ctrl+Shift+Enter to 'enter'
it.
Then
select C2 and do the identical procedure with this formula...

=INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

Now select both B2 and C2, then hover the cursor over the little black
square in the bottom right corner of the selection. When the cursor
turns
to
a small black "cross", click and drag the selection down to the row
where
the last activity is located, then release the mouse button. Your
start
and
end dates should now fill in.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Hi Rick, I am sorry I don't understand. Do I copy your formula into
columns
b2 and c2?

"Rick Rothstein" wrote:

Use these array-entered** formulas where indicated and then copy
them
down...

B2: =INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))
C2: =INDEX(D$1:Z$1,MAX((D2:Z2<"")*COLUMN(D2:Z2))-3)

**commit both formulas using Ctrl+Shift+Enter, not just Enter by
itself

Change the last column from the Z's that I used to your actual last
date
column.

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
Thank you Rick

Basically what I need to do is:

Our people plan their work in excel by putting a task and then
allocating
hours in each month they will be working. Sometimes they skip a
month
or
two
and then restart. I need to look at each row and see the first
month
the
hours appears in and put this a start date. Then I need to look
at
which
month their hours end and put this as the finish date. The only
way
I
know
how to do it is manually. The end result is to do a quick view in
MS
project
gantt.
Hope this is clear.

"Rick Rothstein" wrote:

Your question is not entirely clear (which may be due to the way
my
newsreader has formatted your example data)... can you provide a
little
more
information about what you have, where you have it and what you
want
it
to
look like afterwards?

--
Rick (MVP - Excel)


"Marylou" wrote in message
...
is there a macro that will put the start date and finish date
for
each
of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

Macro start and finish date from rows
 

Rick you rule!

Macro start and finish date from rows
 

On Jul 19, 12:34�am, "Rick Rothstein"
wrote:
<snip
=INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))
<snip

What is the effect of the "--" (minus,minus) operator. I've not met
that before.

Alan Lloyd

Macro start and finish date from rows
 

Double unary minus:
a.. http://www.mcgimpsey.com/excel/formulae/doubleneg.html
b.. http://xldynamic.com/source/xld.SUMPRODUCT.html

--
David Biddulph

wrote:
<snip

What is the effect of the "--" (minus,minus) operator. I've not met
that before.

Alan Lloyd

On Jul 19, 12:34?am, "Rick Rothstein"
wrote:
<snip
=INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))

Macro start and finish date from rows
 

On Aug 17, 7:42�am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Double unary minus:
� a..http://www.mcgimpsey.com/excel/formulae/doubleneg.html
� b..http://xldynamic.com/source/xld.SUMPRODUCT.html

Thanks - its somewhat esoteric - ABS() would be much clearer. As a
programmer who believes that clarity is all, I would never use an
obscure language-dependant operator <g.

BTW b directs me to some domain-name seller.

Alan Lloyd

Macro start and finish date from rows
 

If you're only trying to distinguish 0 from 1, then ABS() would work, but
that doesn't work in other cases where -- works, such as converting a text
representation of a number (including negative numbers) into a real number.
There are many other options, including the use of +0 or *1. The choice of
which (if any) to use is with the user, of course.

Thanks for pointing out the problem with xldynamic. I guess that they've
got a problem (hopefully temporary) with their DNS.
--
David Biddulph

wrote:
On Aug 17, 7:42?am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Double unary minus:
? a..http://www.mcgimpsey.com/excel/formulae/doubleneg.html
? b..http://xldynamic.com/source/xld.SUMPRODUCT.html

Thanks - its somewhat esoteric - ABS() would be much clearer. As a
programmer who believes that clarity is all, I would never use an
obscure language-dependant operator <g.

BTW b directs me to some domain-name seller.

Alan Lloyd

Macro start and finish date from rows
 

On Aug 17, 9:07�am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
If you're only trying to distinguish 0 from 1, then ABS() would work, but
that doesn't work in other cases where -- works, such as converting a text
representation of a number (including negative numbers) into a real number.

Macro start and finish date from rows
 

Well, I'm not replying MVPly, and some would argue that I'm not
advising expertly either, but my advice would be not to use ABS() in
place of the double unary minus, because if my text number is a
negative one ABS turns it positive while the double unary minus leaves
it negative.
--
David Biddulph

On 17 Aug, 09:28, " wrote

ABS() works for for me (Excel97 s2) for simple conversion of positive
or negative text ("" or ') to a real number.

Particularly as inExcelfunctions (where comments in the code is not
possible) I think clarity is essential (even going to hidden columns
instead of very complex functions).

For those who are expertly or MVPly helping inexperienced users, I
think that they have a duty to be explicitly clear in their advice.

Alan Lloyd

On Aug 17, 9:07 am, "DavidBiddulph" <groups [at]biddulph.org.uk
wrote:

If you're only trying to distinguish 0 from 1, then ABS() would work, but
that doesn't work in other cases where -- works, such as converting a text
representation of a number (including negative numbers) into a real number.
There are many other options, including the use of +0 or *1. The choice of
which (if any) to use is with the user, of course.