A long time ago, a kind person in this ng helped me with a substitution code
in another spreadsheet that looks like this:
=IF(A8<>"",A8&"."&CHOOSE(WEEKDAY(DATEVALUE(SUBSTITUTE(A8,".","/"))),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")
The purpose was to get a personal shorthand similar to yyyy.mm.dd.ddd where
ddd would be Mn, Tu, Wd, Th, Fr, Sa, Sn.

This worked for a long time but don't know what has changed as it hasn't
worked for a couple of years or more now.



In a new spreadsheet, I hope to add this personal date format to a cell. In
this one, cell A2's date of 2007.08.06 shows via regular custom date
formatting as Mon.Aug.06.2007 in A2. B1 is the cell that needs the special
custom formatting of 2007.08.06.Mn, etc. The formula in B1 is:

=IF(A2<>"",A2,"")

Just for the sake of seeing if this calculation would also fail, I plugged
in this modified version of the B1 code:
=IF(A2<>"",A2&"."&CHOOSE(WEEKDAY(DATEVALUE(SUBSTITUTE(A8,".","/"))),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")

and, sure enough, it doesn't work in this sheet either.

I get "#VALUE!" in B1.

Wonder what the #VALUE! means here ... Is there a way to fix this?

Cheers and thanks so much! D

the formula assumes that the cell holds a string value that looks like a
date. Since your cell stores an actual data serial number which can be
formatted, the formula would be:

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

--
Regards,
Tom Ogilvy


"StargateFanFromWork" wrote:

> A long time ago, a kind person in this ng helped me with a substitution code
> in another spreadsheet that looks like this:
> =IF(A8<>"",A8&"."&CHOOSE(WEEKDAY(DATEVALUE(SUBSTITUTE(A8,".","/"))),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")
> The purpose was to get a personal shorthand similar to yyyy.mm.dd.ddd where
> ddd would be Mn, Tu, Wd, Th, Fr, Sa, Sn.
>
> This worked for a long time but don't know what has changed as it hasn't
> worked for a couple of years or more now.
>
>
>
> In a new spreadsheet, I hope to add this personal date format to a cell. In
> this one, cell A2's date of 2007.08.06 shows via regular custom date
> formatting as Mon.Aug.06.2007 in A2. B1 is the cell that needs the special
> custom formatting of 2007.08.06.Mn, etc. The formula in B1 is:
>
> =IF(A2<>"",A2,"")
>
> Just for the sake of seeing if this calculation would also fail, I plugged
> in this modified version of the B1 code:
> =IF(A2<>"",A2&"."&CHOOSE(WEEKDAY(DATEVALUE(SUBSTITUTE(A8,".","/"))),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")
>
> and, sure enough, it doesn't work in this sheet either.
>
> I get "#VALUE!" in B1.
>
> Wonder what the #VALUE! means here ... Is there a way to fix this?
>
> Cheers and thanks so much! D
>
>
>