Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
populate month-yyyy when compare 2 cells
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
populate month-yyyy when compare 2 cells
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
populate month-yyyy when compare 2 cells
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
populate month-yyyy when compare 2 cells
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")),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format cells from mm/dd/yyyy to yyyy | New Users to Excel | |||
Excel function to convert month-day-year to mm/dd/yyyy | Excel Worksheet Functions | |||
How to show only Month of a mm/dd/yyyy format? | Excel Discussion (Misc queries) | |||
Converting Day, Date Month Year format to MM/DD/YYYY | Excel Worksheet Functions | |||
I can't convert from mm/dd/yy to month dd, yyyy | Excel Discussion (Misc queries) |