Fairly Easy Formula Question....

[Zorro]

......the answer to which is eluding me!

I am using this formula =VALUE(C1&" "&A1) to return a date (month in col A
and day in col C). Can someone tell me how to modify the formula to avoid
#VALUE! being returned when col C contains an empty cell.

TIA
Zorro

 

[David McRitchie]

What exactly do you have in C1.
How is C1 formatted and what did you enter (type in) for a "month"

 

[Bob Phillips]

Zorro,

It's not an empty C1 causing the problem, it's trying to make a value of
text.

Why are you using value? Why not just use =C1&" "&A1?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Zorro]

C1 contains a number (1 to 31, formatted as a number). The month column is a
formula which returns the sheet name. There is a sheet for each month named
Jan, Feb, Mar etc. Thus the formula I'm having trouble with returns the
combination of the month and day cell which works fine except when the day
cell is empty. I know that by putting ,"" somewhere, it might help, but I'm
lost!

Cheers

 

[Zorro]

"VALUE" function returns a windows date value rather han text. I need to
sort the Date column.

 

[Bob Phillips]

If you want no value when C1 is empty you could use

=IF(C1<>"",VALUE(A1&" "&C1),"")

If you want a valid date, this assumes this month

=IF(C1<>"",VALUE(A1&" "&C1),VALUE(A1&" "&TEXT(TODAY(),"mmm")))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mike A]

Another way:

=IF(C1<>"",VALUE(CONCATENATE(A1," ",C1)),VALUE(CONCATENATE(A1," ",1)))

If C1 is empty, this will use the first day of the month.

If you want no value when C1 is empty you could use

=IF(C1<>"",VALUE(A1&" "&C1),"")

If you want a valid date, this assumes this month

=IF(C1<>"",VALUE(A1&" "&C1),VALUE(A1&" "&TEXT(TODAY(),"mmm")))

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address