Format cell based on input of another cell

[WLHagen]

For all -

I'm using Excel 2010 and trying to assist our payroll department with insurance billing to our accounting department. I'm trying to have one cell that HR uses to enter the date of the billing, which could be an actual date (such as 08/26/2014) or a month (such as 08/2014 or Aug 2014).

I enter this billing date in cell J8. I need this date information to flow over to cells E2:E19. My formula in the cells in column E is =$J$8. I would like format the cells in E to display EXACTLY what is in J8 as listed above. I've tried formatting Column E to Date, but then if J8 = 08/2014, the E cells and J8 display 08/01/2014, not 08/2014. I know I can use an accent (') before my entry to indicate text, but is there an easier way to automatically format the column E cells to display Exactly what is in J8?

Thanks in advance for any and all assistance!

 

[Joe4]

If your format the cell you are entering your value in (J8) as Text before entering your data, then any reference to it (like =$J$8) should return that value in J8, exactly as it appears.

 

[WLHagen]

OOps.... I mis-spoke (mis-typed?). E3 contains the formula =J8. All the rest of the cells in column E contain the formula =$e$3. All of column E is formatted (at present) as General. If I type 10/2014 (without a ' before it) J8 shows 10/1/2014, and all of column E shows 41913 (the "Excel date" for 10/1/2014).

 

[Joe4]

I cannot reproduce that behavior. It shows "10/2014" in all my cells when I try what you said.

What is your formatting on cell J8?

What do these formulas return?
=ISNUMBER(J8)
=ISNUMBER(E3)