Change dates to a custom format via formula ... how to?

Hello,

A2 has formula =NOW()
which makes date today in this format:
Tue.Apr.26.2011

How can I get my custom date formats so that the above date shows up
as Tu.Apr.26.2011.

In another sheet, I was kindly given this to make these types of
changes:
=IF($A$2<>"",TEXT($A$2,"yymmdd.")&CHOOSE(WEEKDAY($A$2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")

I tried this,
=NOW()&CHOOSE(WEEKDAY($A$2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"), but
that didn't work <g>, not that I thought it would.  But was wondering
how I can do this for this workbook and others in future.

Thanx!  :oD

0
4/26/2011 5:47:29 PM
excel 39879 articles. 2 followers. Follow

5 Replies
1510 Views

Similar Articles

[PageSpeed] 44

On Tue, 26 Apr 2011 13:47:29 -0400, StargateFan <IDon'tAcceptSpam@NoJunkMail.com> wrote:

>Hello,
>
>A2 has formula =NOW()
>which makes date today in this format:
>Tue.Apr.26.2011
>
>How can I get my custom date formats so that the above date shows up
>as Tu.Apr.26.2011.
>
>In another sheet, I was kindly given this to make these types of
>changes:
>=IF($A$2<>"",TEXT($A$2,"yymmdd.")&CHOOSE(WEEKDAY($A$2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")
>
>I tried this,
>=NOW()&CHOOSE(WEEKDAY($A$2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"), but
>that didn't work <g>, not that I thought it would.  But was wondering
>how I can do this for this workbook and others in future.
>
>Thanx!  :oD

If you always want to print out today's date:

=CHOOSE(WEEKDAY(TODAY()),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(TODAY(),"\.mmm.dd.yyyy")

Note that I use TODAY() instead of NOW() as NOW() also includes the time of day, which is unneccessary in this formula.

If you want to print out the data that is in A2, then merely substitute A2 for TODAY() in the above formula:

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

And if you want to print a blank if A2 is empty, nest the above into an IF statement:

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

or, better to test if there is a number in A2 since dates are stored as numbers.  This would also print a blank, instead of an error, if A2 is text or error.

=IF(ISNUMBER(A2),CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(A2,"\.mmm.dd.yyyy"),"")
0
ron6368 (329)
4/26/2011 6:10:03 PM
> A2 has formula =NOW()
> which makes date today in this format:
> Tue.Apr.26.2011
>
> How can I get my custom date formats so that the above date
> shows up as Tu.Apr.26.2011.

Here is a formula for you to try...

=MID("SnMnTuWdThFrSa",2*WEEKDAY(TODAY())-1,2)&TEXT(TODAY(), "\.mmm.dd.yyyy")

Rick Rothstein (MVP - Excel) 

0
4/26/2011 6:43:20 PM
On Tue, 26 Apr 2011 14:10:03 -0400, Ron Rosenfeld <ron@nospam.net>
wrote:

>On Tue, 26 Apr 2011 13:47:29 -0400, StargateFan <IDon'tAcceptSpam@NoJunkMail.com> wrote:
>
>>Hello,
>>
>>A2 has formula =NOW()
>>which makes date today in this format:
>>Tue.Apr.26.2011
>>
>>How can I get my custom date formats so that the above date shows up
>>as Tu.Apr.26.2011.
>>
>>In another sheet, I was kindly given this to make these types of
>>changes:
>>=IF($A$2<>"",TEXT($A$2,"yymmdd.")&CHOOSE(WEEKDAY($A$2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")
>>
>>I tried this,
>>=NOW()&CHOOSE(WEEKDAY($A$2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"), but
>>that didn't work <g>, not that I thought it would.  But was wondering
>>how I can do this for this workbook and others in future.
>>
>>Thanx!  :oD
>
>If you always want to print out today's date:
>
>=CHOOSE(WEEKDAY(TODAY()),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(TODAY(),"\.mmm.dd.yyyy")
>
>Note that I use TODAY() instead of NOW() as NOW() also includes the time of day, which is unneccessary in this formula.
>
>If you want to print out the data that is in A2, then merely substitute A2 for TODAY() in the above formula:
>
>=CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(A2,"\.mmm.dd.yyyy")
>
>And if you want to print a blank if A2 is empty, nest the above into an IF statement:
>
>=IF(A2="","",CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(A2,"\.mmm.dd.yyyy"))
>
>or, better to test if there is a number in A2 since dates are stored as numbers.  This would also print a blank, instead of an error, if A2 is text or error.
>
>=IF(ISNUMBER(A2),CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(A2,"\.mmm.dd.yyyy"),"")

Awesome, thank you for so many options.  I use this short date format
all the time so it's neat to know how to display it now.

Thx.

0
4/28/2011 2:17:30 PM
On Tue, 26 Apr 2011 14:43:20 -0400, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>> A2 has formula =NOW()
>> which makes date today in this format:
>> Tue.Apr.26.2011
>>
>> How can I get my custom date formats so that the above date
>> shows up as Tu.Apr.26.2011.
>
>Here is a formula for you to try...
>
>=MID("SnMnTuWdThFrSa",2*WEEKDAY(TODAY())-1,2)&TEXT(TODAY(), "\.mmm.dd.yyyy")
>
>Rick Rothstein (MVP - Excel) 

Thank you!  This worked wonderfully.  I didn't which code to choose so
I created a copy of the worksheet and put your code in one and Ron
Rosenfeld's in the other.  That gives me two options to fall back on
when I re-use this code again in future. In the meantime, this does
the current job just great.  Thx.

0
4/28/2011 2:20:05 PM
On Thu, 28 Apr 2011 10:17:30 -0400, StargateFan <IDon'tAcceptSpam@NoJunkMail.com> wrote:

>Awesome, thank you for so many options.  I use this short date format
>all the time so it's neat to know how to display it now.
>
>Thx.

Glad to help.  Thanks for the feedback.
0
ron6368 (329)
4/29/2011 12:12:53 AM
Reply:

Similar Artilces:

Update End Date
How can I automatically update the End Date to display the create date from a record previously reported. For example the end date for the first record listed below should be 9/24/2009 12:41:00 PM and the end date for the third record listed below should be 8/21/2009 9:00:00 AM. ID CATEGORY DURATION CREATE_DATE END_DATE 3426 Service 24 months 9/15/2009 9:35:00 AM 3426 Service 24 months 9/24/2009 12:41:00 PM 3426 Service 36 months 8/20/2009 12:23:00 PM 3426 Service 36 months 8/21/2009 9:00:00 AM Answered in the earlier pos...

If Statement
Am using the following formula to determine which quarter a payment was made. The first statement returns true, however, the second returns false and the date is 1/21/10. =IF(AND(G15>12/31/09,G15<4/1/10),"1Q","Continue") Thanks! >=IF(AND(G15>12/31/09,G15<4/1/10),"1Q","Continue") Your dates aren't being evaluated as dates. They're being evaluated as the math operation: 12 divided by 31 divided by 9 = 0.043010752688172 4 divided by 1 divided by 10 = 0.4 So, if G15 = the date 1/21/2010 then: AND(G15>12/31/09...

Cell format #3
Want the cell dosen't show anything then the value is zero. How I do that ?? // Sokoban ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Hi Sokoban, If I understand you correctly, just wrap your formula inside an IF function: =IF(YOUR_FORMULA=0,"",YOUR_FORMULA) or You can goto Tools>Options>View>Zero Values Biff >-----Original Message----- >Want the cell dosen't show anything then the value is zero. >How I do that ?? > ...

Match & Index function
Hello - I am trying to use the Match and index functions together. Typically, I have been able to match and pull in the data without a problem. But now that I am using a text file that I export into excel, I am running into problems. Basically, the formula won't work (even though I have tried trimming the data, changing all columns to text, general, and number. The only way that the formula works is when I copy and paste the actual reference cell into the reference array from the range that I am seeking to pull in the data. So for example, my formula reads: =INDEX(Sheet2!B:B,MATCH(Shee...

How to change plot area size of pivot chart
I have not been able to find a way to change the size of the plot area of a pivot chart. Is there a way to do this? thx, Jua No, not without stretching the entire chart. This is one of the limitations of pivot charts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "juamig" <juamig@discussions.microsoft.com> wrote in message news:42BC8AA2-D06D-49B7-B265-3231193167D4@microsoft.com... >I have not been able to find a way to change the size of the plot area of a > pivot chart. Is ...

Conditional Formatting with Style
Worksheet 1 has a dropdown list that uses a lookup function to retrieve data from Worksheet 2. There is one graph in Worksheet 1 that is based on the numbers retrieved. The problem we have is that the numbers in Worksheet 2 can be dollars or percentages or numbers. The graph on Worksheet 1 will always reflect the formatting that is in Worksheet 1, of course...so it needs to be changed manually when the dropdown choice changes from a dollar field to a percentage field, from number to dollar, etc. I've tried conditional formatting, but I don't think you can use it with Style. I'...

font changing in style
I am a novice user of publisher 2002 and have set up a text style using Times new Roman 10.5. It works for many paragraphs in my document and then suddenly it starts using Times to Rome and 12. I've tried making a new style with 10.5, but it does the same thing. Some extra text boxes had appeared in the text box in which this first occurred and I have removed those text boxes (objects) but wonder if there is some sort of overriding factor now for the rest of the document insisting on using font size 12. Alexa On Tue, 21 Feb 2006 12:02:57 +0000, Alexa McLaughlin wrote (in article &l...

x Axis inserts extra dates
Hello, I am charting values in two date columns, one date is 8/16/05 and the other one is 9/1/05. The columns are side by side. This use to be a piece of cake for me until I started using Excel 2003. Now, instead of showing two dates on the X axis, it shows values for every two days between 8/16/05 and 9/1/05. I found out that the X Axis scale was showing a 2 day interval, however I was not able to make any changes in the scale area to show only the two dates I wanted. Any help would be appreciated. thx/Jerry Jerry, If you have only two dates, then there's no reason to have a time...

Disable Mail Format tab
We are running Outlook 2000 and I'm trying to disable the Mail Format tab under options. Any ideas? I can gray out the Options tab by disabling the Object ID using Outlook Administrative Template but can't find the Object ID for the Mail Format tab. Thank You, Jeff Fisher You can't disable an Options page, but you can disable most if not all the options on that page by setting their values with group policy objects. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlo...

simple formula question?
I have a column where each cell has either a X or is blank. I would like to count the number of X's and then return a percentage complete based on the number of cells total (X's and blanks). I also have another column, but this column has all different characters (not just a X) I would still like to count the cells with data and give an average based on number of cells total (X's and blanks). Thanks in advance One way: =COUNTIF(A1:A25,"X")/(COUNTIF(A1:A25,"X")+COUNTBLANK(A1:A25)) Give the cell a percentage format. And if I understand the second ques...

Regression Formula
I'm trying to understand the Regression function- would someone be able to explain the steps to calculate Regression and the info the formula generates? Appreciate any help. Abby - Start with Excel's offline and online Help, and there's much information available on the web. Use bing or Google to search for "excel regression" (without the quotes). If you have specific questions, post again. - Mike http://www.MikeMiddleton.com "Abby" <Abby@discussions.microsoft.com> wrote in message news:01625448-EACD-44B7-BD3E-DA99F58A5FEE@micro...

Linking two spreadsheets with different print format
Hello, Ok, here is the story lets say I have a two spreads one called a.xls and is formated to print on a ll x17 paper and the other to be printed on a 8.5 x 11 paper which is called b.xls. The format are slightly different because the headings for the ll x 17 are at different rows then the 8.5 x 11 which makes the spreadsheet different also the page breaks are different. Since I don't like the print area command I was wondering is there away to link the 11 x 17 format to the 8.5 x 11 format so that if I make one change in the data in master 11 x 17 page it will show up in the 8.5 x 11...

Formulas & Functions
I am trying to have a formula with the following datas: IF C2 equal "Blue"; Sheet1!G3; and make A1=D2. I typed the formula like this: IF(C2="Blue";Sheet1!G3);AND(A1=D2) What I need is to have two results with just one command, that means, when I type Blue on C2, G3 will mark Blue and the content of cell A1 should be transported to the blank cell D2 I get FALSE, because instead to MAKE, the formula is reading as if A1 is equal to D2, and my goal is to transport the content of the cell A1 to the blank cell D2. I would thank very much to whom will help me. Thanks in advan...

default directory change for full text search
Hi Would like to know how i can change the default directory for full text search. Does anyone know how to do this in SQL Server 2008? Regards, Rikard ...

Custom Stored Procedures
If we write our own stored procedures, what will happen to them during the next GP upgrade? Will they remain as is? Elaine wrote: > If we write our own stored procedures, what will happen to them during the > next GP upgrade? Will they remain as is? Hi Elaine, You can build your own SP in the SQL server, wherever they are useful (System DB, company DB or Dynamics DB), and they shouldn't be affected in such by a migration of update. Keep in mind that what's working today won't necessary work tomorrow after an update. The behavior of application specific SP&...

Change the From address
We have an Exchange 2003 server and Outlook 2003. We have a user called Bob Smith. When Bob sends mail, people receive it and see it is from Bob Smith. This is found in the display name field in the General tab in ADUC. We want anyone that gets an email from Bob to see that it came from Information Officer, not Bob Smith. We have changed the display name, but it doesn't make any difference. Is there something else we have to do? Thanks, Neal Hi that should do it, try to rebild RUS to speed it up Nebojsa Seslija "Neal" <neal_@_ap0yar_dot_co_dot_nz> wrote i...

DST Registry Changes
What registry changes does the OS patch for DST add/change? For example, if i wanted to check a computer by connecting to its registry to verify if the OS patch has been applied, where do I look? That'd be easy to find out by applying KB 931836 on a computer and looking at the registry. You can use a tool like Sysinternals (now acquired by MS) RegMon to capture the reg changes. Reg keys to look at : HKLM\System\CurrentControlSet\Control\TimeZoneInformation DaylightStart StandardSite both are REG_BINARY values Also: HKLM\Software\Microsoft\WindowsNT\CurrentVersion\Time Zones\ - Ala...

Re: Percent change
I have written an excel spreadsheet. There are six columns which represent activity during particular days of the week on one worksheet. I have an defined allotment to use each day during the week. Sometimes my weeks are made up of 6 days, sometimes 5 or 4 or 3 or 2 or 1. I have written excel to spread the allotment evenly among the defined days of the week. The day is allowed to vary. Example, Sat, Mon, Tue or Sat, Tue, Wed, Thur, Fri or Wed, Thur, Fri. What I want to do is have the ability to split the allotment by various percents which will add up to 100% for the total number of d...

Too many different cell formats #10
Dear All I have a quite big excel and i am trying to make some changes in format and i receive the message "Too many different cell format" Probably i have many different cell formats. From where i can set up the format in order to know what must be the format of the excel file? I need away to identify the format from all sheets in order to give to the all sheets as common format as i can. Any ideas? ...

Date format issue #2
Hi all, i have a station where the regional parameters show date as yy/MM/dd, ie : 05/07/18 When i open it on my station, where dates are shown like yyyy/MM/dd, i endup with 2018/07/05, which is obviously the wring date. How can i correct that in about 100 excel files. I will then make sure all offices run the same schem for regional parameters. Thanks for your help -- DanielHurtubise ------------------------------------------------------------------------ DanielHurtubise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27449 View this thread: http://www.excel...

Time format #4
I have a cell which is formatted to time as hh:mm Now, the user has to type the time in the above format, hh:mm as above. Is it possible for me to allow the user to simply type in the time as hhmm and then for the spreadsheet to automatically format it to hh:mm. So for example, 08:30. I would like the user to be able to type in either 08:30 or 0830 and for the spreadsheet to recognise both entries and format it to 08:30. Any ideaS? Try to find your earlier message, you already got answers http://www.cpearson.com/excel/DateTimeEntry.htm it is only possible using VBA -- Regards, Pe...

Why does my calculation option in Excel keep changing to manual?
Under Tools->Options->Calculation the option for "manual" keeps being reverted to, even though I never change it. Excel takes it's calculation state from the first workbook you open after launching an Excel session. It may be that your personal.xls, or a timesheet file you use every day that launches automatically has been saved whilst in manual mode. Find and fix. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 --------------------------------------------------------------------------...

counting dates in Excel
I need Excel to take our "call dates" and project 120 days from that call date into another cell For example If my "call date" is on 04/09/04 I need Excel to calculate 120 days from this date Try this formula: =VALUE(Call Date)+120 Make sure on the cell you enter this formula into you set the cell format to date. --- Message posted from http://www.ExcelForum.com/ ...

The columns names on my sheet have changed!!
The columns names on my sheet are in numeric order how can I change them back to alphabetical order? Tools > Options > uncheck R1C1 Reference style box Vaya con Dios, Chuck, CABGx3 "so_moody" <so_moody@discussions.microsoft.com> wrote in message news:6DEAE0E1-A403-418F-856C-13EFBA80C7C1@microsoft.com... > The columns names on my sheet are in numeric order how can I change them back > to alphabetical order? > > hi, Tools>Options>general tab>uncheck R1C1 reference style Regards FSt1 "so_moody" wrote: > The columns names on my s...

<= changes to <?
I have an Access application in which I invoke a report or a form with a where clause. In all cases the where clause is of the form: ServiceDate >= #01/01/2007# And ServiceDate <= #09/25/2007# This runs just fine. I transmitted the MDB file to a client using FTP. when he received it, the <= in all cases had changed to <? in the compiled code ONLY. The source was still correct. I had to go into each code module and force a source change so it would recompile. Any ideas on how or why this happened? -- Bill McCarthy wpmATnorthcst.com I wonder if you may have more luck...