Text and Date in Same Cell?

[jdpro]

Hi,

I would like to enter a date and text in the same cell. When I auto-fill a series of dates with my preferred date format, and then attempt to enter text in some of the cells, the date format changes. I want to have 6-May, but when I enter text to follow the date, it changes to 5/6/2016. In each cell that I want to include text I am forced to retype the date manually to get it to format the way I want it to.

Is there a way to resolve this?

Thank you in advance for any help!

 

[MUKESHY12390]

would you mind to give some examples

 

[jdpro]

Apologies, I should have included. I have a List of dates in cells and in some of the cells need it to show the date and "Cancelled" below the date within the same cell. If I double-click after the date it changes format to the 5/6/2016 instead of 6-May. I Alt+enter to get to second line and enter "Cancelled" but then have to manually fix the date as 6-May and it appears to remove the date formatting in that cell.

 

[FDibbins]

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Fri 06 May 2016) is actually 42496

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

When you combine text to any numeric value, the whole thing becomes text, and text cannot "increment" when copied down

If you want to include text with a date, put the date in it's own cell, reference it and add the text
="some text "& text(A1,"dd/mm/yy")
or, based on your last post...
=text(a1&"d mmm")&" cancelled"

 

[MUKESHY12390]

What I understood .... you want DATE with Some Remark like Cancelled.

=TEXT(C4,"mm dd yyy")  &" " &"Cancelled"

And also you can change Date format according to need

hope it will help you

 

[MUKESHY12390]

opps.. someone got answer already

 

[jdpro]

What I understood .... you want DATE with Some Remark like Cancelled.

=TEXT(C4,"mm dd yyy")  &" " &"Cancelled"

And also you can change Date format according to need

hope it will help you

Thanks to both of you for your assistance!

I can put "cancelled" on the same line if it is not possible to put a hard return in the formula.

Is there a way to reference the date that is already in the cell? I went ahead and entered the date in a separate cell outside the print area and referenced it in the formula but that seems a tad inelegant and it seems there may be a cleaner way than to manually type the date references for each time the class is cancelled. If not I can work with this. Thank you for your help!

 

[MUKESHY12390]

here we're giving reference from cell =TEXT(C4,"mm dd yyy") &" " &"Cancelled"

 

[FDibbins]

seems a tad inelegant and it seems there may be a cleaner way than to manually type the date references for each time the class is cancelled.

Why not put all your dates in the helper, and then use something like....
=text(a1&"d mmm")&if(B1="cancelled"," cancelled","")

 

[jdpro]

FDibbins thank you,

I am a little unclear on the references. I'm new at this so if this sounds very basic that is the reason. I am trying to learn more about the meanings of the different elements in excel formulas.

When you refer to the locations (A1 & B1), what are they in relation to? For example, the cells that have cancelled classes are: A17, A22, A29 for weekly classes that fall on Monday. I need to cancel these for the Monday holidays.

In Column D in each of the above referenced rows, I entered the dates for reference: D17, 22 & 29. So here is the entry in the formula bar for cell A17:

=TEXT(D17,"d-mmm")  &"  "&"CANCELLED"

When you say 'helper' I am assuming that you are referring to the D cells that I put data in for reference. Is that correct?

I would like to try your suggestion

Why not put all your dates in the helper, and then use something like....
=text(a1&"d mmm")&if(B1="cancelled"," cancelled","")

but not sure what a1 & b1 would correspond to in my situation.

Thank you for helping a newbie! For future questions I will make a point of giving exact locations.