Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Days of the week's special format (i.e., Mon = Mn, Tue = Tu ... Sat = Sa, Sun = Sn)?
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(SUBSTIT UTE(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(SUBSTIT UTE(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! :oD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Days of the week's special format (i.e., Mon = Mn, Tue = Tu ... Sa
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(A 2),"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(SUBSTIT UTE(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(SUBSTIT UTE(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! :oD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Days in Text to Dates SPECIAL | Excel Worksheet Functions | |||
SPECIAL NUMBER FORMAT | Excel Discussion (Misc queries) | |||
Excel 2003. Custom format gets replaced by Special format. | New Users to Excel | |||
Given today's date, I want this week's Monday's date | Excel Worksheet Functions | |||
How do I keep each week's data from the payroll calcualtor? | Excel Worksheet Functions |