Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 376
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
format cells from mm/dd/yyyy to yyyy bbane New Users to Excel 3 April 2nd 09 03:42 PM
Excel function to convert month-day-year to mm/dd/yyyy sweetthing4luc Excel Worksheet Functions 7 October 15th 08 10:41 PM
How to show only Month of a mm/dd/yyyy format? yhoy Excel Discussion (Misc queries) 4 July 17th 08 01:18 AM
Converting Day, Date Month Year format to MM/DD/YYYY 61Petrus Excel Worksheet Functions 1 February 19th 08 09:47 PM
I can't convert from mm/dd/yy to month dd, yyyy dodgers32 Excel Discussion (Misc queries) 2 January 30th 08 09:19 AM


All times are GMT +1. The time now is 07:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"