Formula to give special date format but with addition?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StargateFan - 17 Nov 2007 21:17 GMT

To get this format of date in cell B1, with my abbreviated days:
071117.Sa
I was given the cell formula of this:
=IF(A2<>"",TEXT(A2,"yymmdd.")&CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")
It's straightforward because it just copies the date from A2 into the
correct date format for the purpose needed in this particular sheet.

However, I have some addition in another cell where I'd like to
slightly change the date format to our particular format to
standardize the display. In this cell, A12, I currently have this
formula:
=IF(A2<>"",A2+1,"")
which gives me this format (due to making custom date format display):
Sun.Nov.18.2007

This is where I need to do my special date format again. But I
haven't figured out how to work with a formula that has addition in
it, so that
=IF(A2<>"",TEXT(A2,"yymmdd.")&CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")
has an A2+1 function in there. I've tried and tried different
combinations but nothing works (I get "#VALUE").

So from this, =IF(A2<>"",A2+1,""), I need to get Sn.Nov.18.2007 type
of date format rather than the usual Sun.Nov.18.2007 hopefully with
something as simple as a modified ...
=IF(A2<>"",TEXT(A2,"yymmdd.")&CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")

Thank you! :oD

Reply to this Message

macropod - 17 Nov 2007 21:30 GMT

Hi StargateFan,

=IF(A2<>"",CHOOSE(WEEKDAY(A2),"Sn.","Mn.","Tu.","Wd.","Th.","Fr.","Sa.")&TEXT(A2,"mmm.dd.yyyy"),"")

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

> To get this format of date in cell B1, with my abbreviated days:
> 071117.Sa
[quoted text clipped - 24 lines]
>
> Thank you! :oD

Reply to this Message

Ron Rosenfeld - 17 Nov 2007 22:37 GMT

>To get this format of date in cell B1, with my abbreviated days:
>071117.Sa
[quoted text clipped - 24 lines]
>
>Thank you! :oD

=IF(A2<>"",TEXT(A2+1,"yymmdd.")&CHOOSE(WEEKDAY(A2+1),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")
--ron

Reply to this Message

StargateFan - 19 Nov 2007 09:50 GMT

>To get this format of date in cell B1, with my abbreviated days:
>071117.Sa
[quoted text clipped - 24 lines]
>
>Thank you! :oD

Super, thanks for the 2 replies very much! Through trial and error
got the 2 cases to work. Knew I was close but just didn't know how to
fix the formulas and the help overcame that. One response gave me
the addition but not the right format, the other gave me the right
format but with no addition <g>. Here is what seems to work:

For 071120.Tu:
=IF($A$2<>"",TEXT($A$2+1,"yymmdd.")&CHOOSE(WEEKDAY($A$2+1),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")

For Tu.Nov.20.2007:
=IF($A$2<>"",CHOOSE(WEEKDAY($A$2+1),"Sn.","Mn.","Tu.","Wd.","Th.","Fr.","Sa.")&TEXT($A$2+1,"mmm.dd.yyyy"),"")

Thanks. Works perfectly, it seems. I found out by this exercise that
customizing the date format created #VALUE! in cells when simply
copy/pasting so I pretty much had to format each cell manually in this
way. A small price to pay for this rather unique requirement for this
one spreadsheet only, but glad there weren't more then 42 entries to
edit <g>. Cheers. :oD

Reply to this Message