Prev: Nesting IF Statement in excel 2007
Next: Receive data in column with pull down
From: AskExcel on 12 May 2010 05:01
Dear Forum,
I have a scenario where I need to compare 2 date cells. When cell1 empty
then take cell2 and format the date month-yyyy in cell3. When cell1 not empty
then take cell1 date and format the date month-yyyy in cell3.

i need advise how can i do that and create macro to automate the change.
example
c d e
20100220 feb-2010

c d e
20100521 20100220 may-2010


From: Roger Govier on 12 May 2010 05:17
Hi

Try
=IF(COUNT(C1:D1),
IF(C1,TEXT(DATE(LEFT(C1,4),MID(C1,5,2),1),"mmm-yyyy"),
TEXT(DATE(LEFT(D1,4),MID(D1,5,2),1),"mmm-yyyy")),"")

All the above should be on one line
--
Regards
Roger Govier

AskExcel wrote:
> Dear Forum,
> I have a scenario where I need to compare 2 date cells. When cell1 empty
> then take cell2 and format the date month-yyyy in cell3. When cell1 not empty
> then take cell1 date and format the date month-yyyy in cell3.
>
> i need advise how can i do that and create macro to automate the change.
> example
> c d e
> 20100220 feb-2010
>
> c d e
> 20100521 20100220 may-2010
>
>
From: Jacob Skaria on 12 May 2010 06:13
If the dates are in excel date format try the below with dates in c2 :d 2
=TEXT(INDEX(C2:D2,MATCH(TRUE,INDEX(C2:D2<>"",),)),"mmmm-yyyy")

If the dates are in text format try the below with dates in c5 : d5

=TEXT("2010/" & MID(INDEX(C5:D5,MATCH(TRUE,INDEX(C5:D5<>"",),)),5,2) &
"/20","mmmm-") & LEFT(INDEX(C5:D5,MATCH(TRUE,INDEX(C5:D5<>"",),)),4)

--
Jacob (MVP - Excel)


"AskExcel" wrote:

> Dear Forum,
> I have a scenario where I need to compare 2 date cells. When cell1 empty
> then take cell2 and format the date month-yyyy in cell3. When cell1 not empty
> then take cell1 date and format the date month-yyyy in cell3.
>
> i need advise how can i do that and create macro to automate the change.
> example
> c d e
> 20100220 feb-2010
>
> c d e
> 20100521 20100220 may-2010
>
>
From: AskExcel on 12 May 2010 21:14
Dear Expert,
I would like to built 1 macro so that I can execute the macro to mass change
it but I do not know how to put this code in visual basic
in between sub() and endsub()
Please help.
Thanks alot

=IF(COUNT(C1:D1),
IF(C1,TEXT(DATE(LEFT(C1,4),MID(C1,5,2),1),"mmm-yyyy"),
TEXT(DATE(LEFT(D1,4),MID(D1,5,2),1),"mmm-yyyy")),"")
 | 
Pages: 1
Prev: Nesting IF Statement in excel 2007
Next: Receive data in column with pull down