| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to specific the cell format?
Refering to the post in general question
Does anyone know how to do it in excel? In cell A1, if text within this cell is "Daily", then the cell B1 will show the date in this format "18-Jan". If cell A1, if text within this cell is "Monthly", then teh cell B1 will show the date in this format "Jan-07" Does anyone have any suggestion? Thank you for any suggestion Eric |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to specific the cell format?
See one answer to your identical post in microsoft.public.excel.misc.
In article , Eric wrote: Refering to the post in general question Does anyone know how to do it in excel? In cell A1, if text within this cell is "Daily", then the cell B1 will show the date in this format "18-Jan". If cell A1, if text within this cell is "Monthly", then teh cell B1 will show the date in this format "Jan-07" Does anyone have any suggestion? Thank you for any suggestion Eric |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to specific the cell format?
=IF(A1="","",IF(A1="Daily",TEXT(TODAY(),"dd-mmm"),TEXT(TODAY(),"mmm-yy")))
"Eric" wrote: Refering to the post in general question Does anyone know how to do it in excel? In cell A1, if text within this cell is "Daily", then the cell B1 will show the date in this format "18-Jan". If cell A1, if text within this cell is "Monthly", then teh cell B1 will show the date in this format "Jan-07" Does anyone have any suggestion? Thank you for any suggestion Eric |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to specific the cell format?
How about this?
=TEXT(TODAY(), IF(A1="Daily", "dd-mmm", IF(A1="Monthly", "mmm-yy", ""))) OR =TEXT(TODAY(), IF(TRIM(A1)="Daily", "dd-mmm", IF(TRIM(A1)="Monthly", "mmm-yy", ""))) which takes care of leading spaces. If you accidentally key in a space and Daily in A1, the first formula will not return day and month but the second formula will. Experts, please feel free to correct me. Epinn "Eric" wrote in message ... Refering to the post in general question Does anyone know how to do it in excel? In cell A1, if text within this cell is "Daily", then the cell B1 will show the date in this format "18-Jan". If cell A1, if text within this cell is "Monthly", then teh cell B1 will show the date in this format "Jan-07" Does anyone have any suggestion? Thank you for any suggestion Eric |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to specific the cell format?
Not sure how you want to use the formula; part of a heading for a report template?
Anyway, word of caution: Since TODAY() is a volatile function and it gets changed every time when the worksheet is accessed, you may want to lock in the result of the formula by doing editpaste specialvalue. Comments welcome. Epinn "Epinn" wrote in message ... How about this? =TEXT(TODAY(), IF(A1="Daily", "dd-mmm", IF(A1="Monthly", "mmm-yy", ""))) OR =TEXT(TODAY(), IF(TRIM(A1)="Daily", "dd-mmm", IF(TRIM(A1)="Monthly", "mmm-yy", ""))) which takes care of leading spaces. If you accidentally key in a space and Daily in A1, the first formula will not return day and month but the second formula will. Experts, please feel free to correct me. Epinn "Eric" wrote in message ... Refering to the post in general question Does anyone know how to do it in excel? In cell A1, if text within this cell is "Daily", then the cell B1 will show the date in this format "18-Jan". If cell A1, if text within this cell is "Monthly", then teh cell B1 will show the date in this format "Jan-07" Does anyone have any suggestion? Thank you for any suggestion Eric |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to specific the cell format?
Actually, there is an easier way than paste specialvalue.
I just learned that I can enter the formula with Ctrl+; (semicolon) to lock in the date. Epinn "Epinn" wrote in message ... Not sure how you want to use the formula; part of a heading for a report template? Anyway, word of caution: Since TODAY() is a volatile function and it gets changed every time when the worksheet is accessed, you may want to lock in the result of the formula by doing editpaste specialvalue. Comments welcome. Epinn "Epinn" wrote in message ... How about this? =TEXT(TODAY(), IF(A1="Daily", "dd-mmm", IF(A1="Monthly", "mmm-yy", ""))) OR =TEXT(TODAY(), IF(TRIM(A1)="Daily", "dd-mmm", IF(TRIM(A1)="Monthly", "mmm-yy", ""))) which takes care of leading spaces. If you accidentally key in a space and Daily in A1, the first formula will not return day and month but the second formula will. Experts, please feel free to correct me. Epinn "Eric" wrote in message ... Refering to the post in general question Does anyone know how to do it in excel? In cell A1, if text within this cell is "Daily", then the cell B1 will show the date in this format "18-Jan". If cell A1, if text within this cell is "Monthly", then teh cell B1 will show the date in this format "Jan-07" Does anyone have any suggestion? Thank you for any suggestion Eric |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to specific the cell format?
I just learned that I can enter the formula with Ctrl+; (semicolon) to lock in the date. <<
I better clarify that the formula is =TODAY() and *not* the ones posted earlier. Sorry for the confusion. Epinn "Epinn" wrote in message ... Actually, there is an easier way than paste specialvalue. I just learned that I can enter the formula with Ctrl+; (semicolon) to lock in the date. Epinn "Epinn" wrote in message ... Not sure how you want to use the formula; part of a heading for a report template? Anyway, word of caution: Since TODAY() is a volatile function and it gets changed every time when the worksheet is accessed, you may want to lock in the result of the formula by doing editpaste specialvalue. Comments welcome. Epinn "Epinn" wrote in message ... How about this? =TEXT(TODAY(), IF(A1="Daily", "dd-mmm", IF(A1="Monthly", "mmm-yy", ""))) OR =TEXT(TODAY(), IF(TRIM(A1)="Daily", "dd-mmm", IF(TRIM(A1)="Monthly", "mmm-yy", ""))) which takes care of leading spaces. If you accidentally key in a space and Daily in A1, the first formula will not return day and month but the second formula will. Experts, please feel free to correct me. Epinn "Eric" wrote in message ... Refering to the post in general question Does anyone know how to do it in excel? In cell A1, if text within this cell is "Daily", then the cell B1 will show the date in this format "18-Jan". If cell A1, if text within this cell is "Monthly", then teh cell B1 will show the date in this format "Jan-07" Does anyone have any suggestion? Thank you for any suggestion Eric |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to specific the cell format? | Excel Discussion (Misc queries) | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| Format of a cell | Excel Discussion (Misc queries) | |||
| Format cell to accept only specific character-reject others | Excel Discussion (Misc queries) | |||
| can't format cell - have tried unlocking and unprotecting | Excel Discussion (Misc queries) | |||
Linear Mode
