Quick Day of the Week Question

[TAPS_MikeDion]

Hi guy's,

Apparently, I have jello for brains at this point, because this seems so simple. Anyway, I have the following formula in cell K14 and want another cell to display the day of the week based on this cell.

Cell K14:

[B]
=TEXT(TODAY()+1,"mmmmmm-dd-yyyy")[/B]

For some reason, using the following formula in cell K11 produces the same text as cell K14.

Cell K11:

[B]=TEXT(MAIL!K14,"ddd")[/B]

I'm looking for this as the end result in the two cells:
Cell 11: Tue
Cell 14: December 4, 2012

Several other sheets access the information in cells 11 and 14.

Thanks in advance!

-Mike
P.S. Jello is good baked.

 

[cml19722000]

Hi guy's,

Apparently, I have jello for brains at this point, because this seems so simple. Anyway, I have the following formula in cell K14 and want another cell to display the day of the week based on this cell.

Cell K14:

[B]
=TEXT(TODAY()+1,"mmmmmm-dd-yyyy")[/B]

For some reason, using the following formula in cell K11 produces the same text as cell K14.

Cell K11:

[B]=TEXT(MAIL!K14,"ddd")[/B]

I'm looking for this as the end result in the two cells:
Cell 11: Tue
Cell 14: December 4, 2012

Several other sheets access the information in cells 11 and 14.

Thanks in advance!

-Mike
P.S. Jello is good baked.

Try key in
=TEXT(TODAY(),"DDD")

It is going to give the desire result that you are looking for...

Thanks.

 

[TAPS_MikeDion]

Try key in
=TEXT(TODAY(),"DDD")

It is going to give the desire result that you are looking for...

Thanks.

Thanks, but that will not work. The formula in cell 14 has the text TODAY()+1 in it, which may be changed to + anything.

 

[Teeroy]

You'll need to use =TEXT(TODAY()+1,"DDD"). MAIL!K14 is not a date, it's just a string. Alternatively you could have the date somewhere else in the workbook and have both TEXT functions access that cell location.

 

[norulen]

How about this:
you can have one cell with the date i.e. TODAY()+1 or + anything you might want
in one cell you can use this formula "=TEXT(A1,"mmmmmm d, yyyy")" [In my example A1 is the cell with this formula "=Today()+1"]
in the other cell you can use this "=TEXT(A1,"ddd")"

You can change the reference cell according to your requirement.
Let me know if it doesn solve the problem

 

[pjmorris]

Hi Mike,

The reason this is difficult is that you're using Text to represent dates - I've seen this elsewhere in the Forum, but still fail to understand the benefits. If I've understood your question, why not do the following:

1. In Cell K14: =Today()+1 then apply a custom format of "mmmm-dd-yyyy"
2. In cell K11: =K14 then apply a custom format of "ddd"

This will achieve what I believe is your desired output while leaving the dates available for use elsewhere.

Hope this helps.

Regards

 

[TAPS_MikeDion]

Thanks to everyone!!!

Peter,

Your suggestion, though it required removing protection from about 20 sheets, changing the format and formula's in each sheet and protecting the sheets again, did achieve the results I was looking for. A little extra work, but the end result was all that mattered.

Thank you,
Mike

 

[pjmorris]

Hi Mike,

Thanks for the feedback - its appreciated.

Hope this isn't teaching granny to suck eggs, but did you know you can highlight all the sheet tabs, make the changes to Cells K11 & K14 on the sheet you can see and it will be applied to all the highlighted sheets? Possibly too late for your current work, but maybe useful in the future.

Regards