Days of the week's special format (i.e., Mon = Mn, Tue = Tu ... Sat = Sa, Sun = Sn)?

  • Thread starter StargateFanFromWork
  • Start date
S

StargateFanFromWork

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! :eek:D
 
T

Tom Ogilvy

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"),"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top