How to specific the cell format?

[Guest]

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

 

[JE McGimpsey]

See one answer to your identical post in microsoft.public.excel.misc.

 

[Epinn]

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

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

 

[Epinn]

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 edit>paste special>value.

Comments welcome.

Epinn

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

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

 

[Epinn]

Actually, there is an easier way than paste special>value.

I just learned that I can enter the formula with Ctrl+; (semicolon) to lock in the date.

Epinn

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 edit>paste special>value.

Comments welcome.

Epinn

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

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

 

[Epinn]

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

Actually, there is an easier way than paste special>value.

I just learned that I can enter the formula with Ctrl+; (semicolon) to lock in the date.

Epinn

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 edit>paste special>value.

Comments welcome.

Epinn

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

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