bring back first date based on month

[aivoryuk]

In cell A1 I have the month (January) In A2 I would like to bring back the first day of cell A1 eg ' 01/01/2011'

Is this possible?

Regards

Alex

 

[Jonmo1]

What exactly is in A1, the TEXT String "January"
Or a DATE within the month of january, formatted to show just the month?

Use this to test
=ISNUMBER(A1)

Is it true or false?

 

[Jonmo1]

If it's a text string, (Isnumber(A1) returned false)
Use
=(A1&1)+0

If it's a date (Isnumber(A1) returned true)
Use
=A1-DAY(A1)+1

 

[aivoryuk]

What exactly is in A1, the TEXT String "January"
Or a DATE within the month of january, formatted to show just the month?

Use this to test
=ISNUMBER(A1)

Is it true or false?

Hi it is text string "January"

 

[aivoryuk]

If it's a text string, (Isnumber(A1) returned false)
Use
=(A1&1)+0

If it's a date (Isnumber(A1) returned true)
Use
=A1-DAY(A1)+1

Hi it bring back the correct day and month but gives me a year of 2001 when I need for it to be 2011 (or current year)

 

[T. Valko]

Hi it bring back the correct day and month but gives me a year of 2001 when I need for it to be 2011 (or current year)

If you want the year to be dynamic...

A1 = January

=DATE(YEAR(NOW()),MONTH(A1&1),1)

 

[Jonmo1]

Hi it bring back the correct day and month but gives me a year of 2001 when I need for it to be 2011 (or current year)

If A1 is a TEXT String of "January" (or any month) then this
=(A1&1)+0
Will return the 1st day of that month in the current year.

What does this formula return

=ISNUMBER(A1)

 

[CharlesChuckieCharles]

=LOOKUP(LOWER(LEFT(A1,3)),{0,"apr","aug","dec","feb","jan","jul","jun","mar","may","nov","oct","sep"},{0,4,8,12,2,1,7,6,3,5,11,10,9})

returns the month number
(and yes it has to be in that order)

you can work how to fill out a Date(year,month,day) on your sheet

 

[aivoryuk]

If A1 is a TEXT String of "January" (or any month) then this
=(A1&1)+0
Will return the 1st day of that month in the current year.

What does this formula return

=ISNUMBER(A1)

It is showing it is false so it is not a number but it was giving me 2001 for the year)

T Valko that worked great thanks very much

 

[T. Valko]

If A1 is a TEXT String of "January" (or any month) then this
=(A1&1)+0
Will return the 1st day of that month in the current year.

What does this formula return

=ISNUMBER(A1)

I'm uncertain as to what the OP means by "current year".

=(A1&1)+0

Today is May 13 2011. So, that formula will return Jan 1 2011.

However, on Jan 1 2012 that formula will still return Jan 1 2011.

If the OP wants the year to change (be dynamic) then a different approach is needed.