Dates

[Tim]

Hello All,

I have a vlookup formula that returns the Month (i.e. May)
based on a serial number. I would like to determine if
the lookup month falls prior to a month that is hard
input. I guess I need to determine a numeric value for
the months???

Thanks in advance

 

[Dave R.]

How about

=MONTH(lookup formula returning date)<MONTH(cell with hard coded date)

 

[RagDyer]

Since you say that the month is based on a serial number, then it is a true
date.

So, if your "hard input" date is a true date, say in B1, and your lookup
date is returned to say A1, then this simple formula should work:

=IF(A1<B1,"Prior","Later")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hello All,

I have a vlookup formula that returns the Month (i.e. May)
based on a serial number. I would like to determine if
the lookup month falls prior to a month that is hard
input. I guess I need to determine a numeric value for
the months???

Thanks in advance

 

[Tim]

My formula does not return a date, it returns the name of
the month. The result of my lookup formula would
be "May". The user also types the name of the other hard
coded month. No actual dates are used only text.

 

[RagDyer]

But you *did* say that it is derived from a serial number.
What kind of "serial number"?
If it's XL's, then even though only the name of the month is displayed, you
should still have a true date *contained* within the cell!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


And you could easily custom format the input cell to "mmmm", and have the
user enter a true date very easily, keying in 10/10/04, for instance.
My formula does not return a date, it returns the name of
the month. The result of my lookup formula would
be "May". The user also types the name of the other hard
coded month. No actual dates are used only text.

 

[Arvi Laanemets]

Hi

The hard input month is like 'January' or 'May' etc of course? You can get
month numbet from it, using MATCH function with month array as parameter.
Something like
=MATCH(MonthName,{"January","February","March",...},0)

I myself prefer all months on sheet to be inserted as dates - 1st of month -
formatted as "mmm" or "mmmm". So is it much easier to use them in various
calculations, or change the month through code.

 

[Tim]

The phrase "serial number" is misleading. I meant that
literally. The serial number is one field in a table.
i.e.
Serial Number Name Month .... ....
556987 Johnson Drills May

I have no control how the month is entered into the table
(it is pulled from external data) and it is text.

I created a table with the months in one column and a
numeric value in the other. I use the numeric value to
determine which month is earlier. This is tedious and I
was hoping to simplify the formula. This is what I have:

"=IF(OR(VLOOKUP(C705,Strats!$A$1:$G$3728,6,FALSE)
=0,VLOOKUP(C705,Strats!$A$1:$H$3728,8,FALSE)
<$N$2),$M$2,VLOOKUP(C705,Strats!$A$1:$G$3728,6,FALSE))"

$M$2 is where the month is entered by the user. $N$2 is
a numeric value based on a vlookup.

It works, but I was hoping for a "slicker" formula.

 

[Daniel.M]

Hi,

Assuming:
you don't care about the years.
A1: the vlookup formula result
A2: the month hard input

=SEARCH(MID(A1&" ",3,2),"nubrrcriy nelygupttovece")< SEARCH(MID(
A2&" ",3,2),"nubrrcriy nelygupttovece")

IMO, you would benefit from working with real dates. Excel is a math-oriented
tool after all (and real dates are treated as real numbers in Excel). But it's
your choice obviously.

Regards,

Daniel M.