Days and dates in Excel

I'm tired of entering slashes to tell Excel that the 
numbers I'm entering are dates and colons to tell Excel 
that it's dealing with times.  I asked a similar question 
here several weeks back and one of the responses gave me a 
link which took me to a site where a software guru had 
posted a formula to do this.  Unfortunately, no one told 
me where to put this in Excel when I cut and paste it from 
the website.

Then I got to thinking that Excel has under soecial 
formats formats to allow fast entry of SSN's and ZIP 
codes.  When you enter nine numbers in a cell formated for 
SSN's it enters the dashes in the appropriate places.  
Where's this format?  If I could find it, I could copy it 
and make the changes I want to the copy, save that as a 
custom format, and format my cells to that.  Please help 
me.  Thanks!
0
phmooney (2)
9/1/2003 2:13:26 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
379 Views

Similar Articles

[PageSpeed] 12

You can use a custom number format "00\/00\/\0000". But don't try to do date arithmetic with
these values. You won't get the correct answers.

if you type 8312003 for Aug 31 2003, you'll have the number 8,312,003. The date serial number
for that date is 37864.


On Sun, 31 Aug 2003 19:13:26 -0700, "Pat Mooney" <phmooney@hotmail.com> wrote:

>I'm tired of entering slashes to tell Excel that the 
>numbers I'm entering are dates and colons to tell Excel 
>that it's dealing with times.  I asked a similar question 
>here several weeks back and one of the responses gave me a 
>link which took me to a site where a software guru had 
>posted a formula to do this.  Unfortunately, no one told 
>me where to put this in Excel when I cut and paste it from 
>the website.
>
>Then I got to thinking that Excel has under soecial 
>formats formats to allow fast entry of SSN's and ZIP 
>codes.  When you enter nine numbers in a cell formated for 
>SSN's it enters the dashes in the appropriate places.  
>Where's this format?  If I could find it, I could copy it 
>and make the changes I want to the copy, save that as a 
>custom format, and format my cells to that.  Please help 
>me.  Thanks!

0
myrnalarson (223)
9/1/2003 3:11:09 AM
You have a fundamental, though common, misconception. Formats 
control how values are *displayed*, only. The parser has separate 
rules about interpreting entries, which are independent of the 
cell's format. 

You can use something similar to SSN formats (which are found in 
Format/Cells/Special...) to make a number look like a time, e.g,:

    Format/Cells/Number/Custom    00:00

which will cause an entry of 1234 to display as 12:34. But it will 
not be recognized as a time by XL.  For instance, if the above were 
entered in A1, then 

A2:     =A1+TIME(1,26,0)

will display 12:34 rather than 14:00, since times in XL are stored 
as fractional days (e.g., 1:26 = 0.059722222, so the stored result 
will be 1234.0597222222). Unless you only want to display the times, 
the format solution will be unacceptable.

The situation is different with SSN's and ZIPs - the SSN is stored 
as an integer, for example:

    012-34-5678

is actually store as the number 12345678. This is OK for most 
purposes, but leads to confusion if you try to export it, since the 
leading zero is not retained. Better to enter SSN's as text strings, 
since you'll never have to do math on them.

As far as no one telling you where to put the code, did you ask? The 
responder(s) probably couldn't tell from your post whether you had 
worked with macros or event macros - and assumed you'd ask for more 
help if you hadn't.

In any case, event macros are the only way within XL/VBA to get to 
where you want to go, at least without using helper cells. I suspect 
your previous answer involved Chip Pearson's event macros:

    http://www.cpearson.com/excel/DateTimeEntry.htm

In addition to an article on the same site that explains where code 
should go:

    http://www.cpearson.com/excel/codemods.htm

you might want to look at David McRitchie's getting started with 
macros page:

    http://www.mvps.org/dmcritchie/excel/getstarted.htm


In article <028c01c3702e$a0ecb5e0$a601280a@phx.gbl>,
 "Pat Mooney" <phmooney@hotmail.com> wrote:

> I'm tired of entering slashes to tell Excel that the 
> numbers I'm entering are dates and colons to tell Excel 
> that it's dealing with times.  I asked a similar question 
> here several weeks back and one of the responses gave me a 
> link which took me to a site where a software guru had 
> posted a formula to do this.  Unfortunately, no one told 
> me where to put this in Excel when I cut and paste it from 
> the website.
> 
> Then I got to thinking that Excel has under soecial 
> formats formats to allow fast entry of SSN's and ZIP 
> codes.  When you enter nine numbers in a cell formated for 
> SSN's it enters the dashes in the appropriate places.  
> Where's this format?  If I could find it, I could copy it 
> and make the changes I want to the copy, save that as a 
> custom format, and format my cells to that.  Please help 
> me.  Thanks!
0
jemcgimpsey (6723)
9/1/2003 3:15:36 AM
Reply:

Similar Artilces:

Changing dd/mm/yy to the day of the week
Hi, Am looking for a macro or whatever which will change a set of dates into the corresponding week day. Ie: I have a column of dates in this format: 01/01/03 thru to 16/10/03. I want to write a quick macro which will then drop into an adjacent cell, the day of the week that each day is, so in column 'A' we have the dates (01/01/03) and in column 'B' we have the day that that date was ie Wednesday. any help would be great. cheers On Thu, 16 Oct 2003 02:13:51 -0700, Gerald wrote: > Hi, > > Am looking for a macro or whatever which will change a set > of ...

Date Find on or Prior to Today
I need a view that will find records if a date field is "on or before" to today. While "on or before" is an operatior, you can only set this to a specific date. Any ideas on how to make this dynamic would be appreciated. Thanks! AAA, If you want to see Dates from anytime prior to the present day, using the Last X Years will suffice. "AAA" wrote: > I need a view that will find records if a date field is "on or before" to > today. While "on or before" is an operatior, you can only set this to a > specific date. Any ideas...

Excel 2003
When I upgraded from Office 2000 to 2003, the Office Shortcut bar disappeared. How do I get it to load at bootup? -- PT ...

Date Calculations
I have to create a formula in excel that takes a date (4-1-2010) and subtracts another date from it (2-1-2008) and gives me the remainder of months. Any ideas? How do you define a month difference? 30 days? Calendar months? What about months with different numbers of days. E.g., how many months between 28-Feb and 31-March. 1? 1+3/30? You need to define how the months should be calculated. At its simplest, just subtract one date from the other and divide by 30. That will give one of several possible answers. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel,...

30, 60, 90 days late and due within 14 days
I am working on a spreadsheet that will calculate late suspenses. Basically I have 3 different types of late suspenses and am looking to calculate between 0 and 30 days late, 30 - 60 days late, 60 - 90 days late and over 90 days and each of these time frames for the three categories. Here is what my spread sheet looks like: Name ID TYPE SUSP DAYS OVER DUE UNIT WOOD 6470 N/A 18-Mar-05 (234) A SMITH 7453 UNIT 22-APR-05 (199) B JONES 9741 CO 1-Nov-05 (6) C ...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

Parsing Excel Documents
I have a number of Excel documents that I would like to extract data from. The data however is no in a CSV layout. There are some rules on how the file is laid out (for example the data rows i want are always after the first empty row after the first lot of data in the file). I wanted to know if there is an exensible consumer of Excel Docuemnts that can be used in an MFC app. I'll be using .NET studio 2002 and the code will be UNmanaged. Thanks in advance. MattC >I wanted to know if there is an exensible consumer of Excel Docuemnts that >can be used in an MFC app. Matt, You...

Format dates
Hello, I would like to format a dates in column A to a specific format (either directly in column A or column B) The format is ddmmy. So for example 18/1/2006 should appear as 18016 and 3/12/2006 should appear as 03126. Is there a way to do this? Using "format cells/date" wont do the trick. Any suggestions please? Thank you, Steven. Hi! Use a CUSTOM format of ddmmy. Select the cells Goto Format>Cells>Number>Custom In that little box on the right type: ddmmy OK out Biff "steven" <steven@discussions.microsoft.com> wrote in message news:2B7AB99C-9...

is it possible to convert excel workbook into a standalone applic.
my business uses a spreadsheet for all its pricing would it be possible for us to convert this to a standalone product or import it as part of a complete business package in access ...

lonking an excel spreadsheet and a word document
Every time I try to link an excel spreadsheet with a word document I copy the spreadsheet, go back to word, paste special, select link and then try formatted text (or for that matter any other format) and get one of two messages, word can't get the data from excel or there is not enough memory. The latter often comes up if I have pasted the spreadsheet without the link first. In other words, if there is already a table in the document then there isn't enough memory to paste again??? Hi, It would be good if you could post back a follow-up message to this thread as to what versions of ...

Customer Vendor Consolidation transfer date
When working in the Customer/Vendor Consolidation window, I am trying to change the Transfer Date to a prior day but the Transfer Date keeps reverting back to the User Date. The only way I have been able to work around this is to change my User Date to the date I want the transfer to post. Is there a problem with this window? We are on GP 10.0 Sue, I saw this issue once in the past where our customer was not registered for Multicurrency. Can you confirm that you do not have Multicurrency registered (or enabled)? Apparently something about that field requires MC. I do not know o...

EXCEL 2007 and 2002
I created a file in EXCEL 2002 and it is being used by someone with 2007. He got a "data may have been lost" when he opened the file. I asked him to send me back a file he created using the file I sent (so I could try to find the source of the problem, knowing that I might not, given that I had only 2002, but it was worth a look) but when I received it, all that was visible was one worksheet with: Permission for this workbook is currently restricted. This workbook can only be opened by using Microsoft Office 2003 or later. You can request the author of the workbook to send a copy ...

Date formatting in Pivot Chart
Hi, I'm trying to create a pivot chart and the date field is not formatting properly - the format comes out different from that of the pivot table. How can I fix this? Format axis doesn't give me an option for date format. In the pivot table, right-click the date field button, and choose Field Settings. Click the Number button, and select a date format Click OK twice The chart should show the selected format. JessicaMc wrote: > Hi, > > I'm trying to create a pivot chart and the date field is not formatting > properly - the format comes out different from that o...

Conditional formatting of dates in two cells using Excel 2007
Hi, I've got a spreadsheet set up as: Column A - task Column B - Expected completion date Column C - Actual completion date and I want to set it up so that all tasks that have not yet been completed but should have been are highlighted in red (that is, for example, if B2< todays date and C2 is blank then A2 is highlighted red). I've been messing about but can't seem to get it right in the conditional formatting - any suggestions? thanks Select cell A2 Select conditional format -> formula Enter the following forumla =AND(B2<TODAY(),C2=&qu...

How do I add the file last modified date into an Excel header?
It is easy to show the date a document was last modified in MS-Word by inserting the field: savedate into the footer. A very common requirement. But there does not appear to be any way, or at least no easy way to do this in Excel. I CAN'T BELIEVE IT! They still haven't added this. Excel forces you to put in the current date. YUK! Why would I want that in my print out? I want to know when it was last modified, not when I printed it. ARG!!! From an earlier posting Hi use the following UDF: Function DocProps(prop As String) application.volatile On Error GoTo err_value ...

How to import this data in two columns of an excel file ?
I have a txt file like this: .. Chapter 1 [1][2] [3][4][5] [27] [28] [29] .. Chapter 2 [1] [18] .. Chapter 3 [1] [14] [20] There's a way to import it in an excel file and have in first coulumn for each row a chapter and in the next column all the rest of the text like that: COLUMN 1 COLUMN 2 1 ROW Chapter 1 [1][2] [3][4][5] [27] [28] [29] 2 ROW Chapter 2 [1] [18] 3 ROW Chapter 3 [1] [14][20] Thanks Adriano I think I'd just bring it into column A and then separate it into columns. Option Ex...

Farsi, Pashto, or Dari Excel 2007 Manual?
I am looking for a hard copy manual for Excel 2007 in Farsi, Pashto, or Dari. I'm in Afghanistan trying to help some folks with Excel basics. A manual would be great! I found some Arabic online training that I can print out, but have a real need for Farsi, Pashto or Dari. Thanks! ...

send mail every days
Hello friends, Have a question about outlook 2007. I need to run a macro every days, this macro send a mail with information of a SQL. Have the macro created and run succesfull but how i can to do for run every days automatically ? is possible schedulle this macro ? Thanks in advnace and sorry by my english, Christian Wis. This might help you: http://www.vboffice.net/sample.html?lang=en&mnu=2&smp=10&cmd=showitem -- Best regards Michael Bauer - MVP Outlook Category Manager - Manage and share your categories: SAM - The Sending Account Manager: &...

Excel Auto-refresh box gone
I have OLAP cubes (pivot tables) in my spreadsheets that can b auto-refreshed when the file opens. However, there is also a messag box that used to open up that would ask if I wanted to Enable/Disabl Auto-refresh. This box is gone now. Anyone know how to get this box back -- Message posted from http://www.ExcelForum.com ...

Correct dates for transactions.
Hello, i don't know if the credit cards are managed differently in other countrys, but here in Mexico, they have a cut date, and a due date to pay the charges. For this when i insert a transaction for a credit card in Money, the date doesn't correspond with the payment date and when i see cash flow reports i can not have the correct charges. I don't know if there is a way in money to set a cut date and a due date for credit cards. In microsoft.public.money, Jorge Hdez wrote: >Hello, i don't know if the credit cards are managed differently in other >countrys, but her...

How do I create a pictograph in Excel?
I am trying to create a pictograph in Excel, but I don't see it as an option on the chart wizard. How can I do this? Hi, You can use a picture as the fill effect. The Format dialog Patterns tab has a Fill Effects button. This allows you to select a image to use and also determine how that image should be display. Such as stretching it to the appropriate height or using the image per unit. For more information see Jon Peltier's explanation. http://peltiertech.com/Excel/ChartsHowTo/CustomStacks.html Cheers Andy ldimes4 wrote: > I am trying to create a pictograph in Excel, bu...

External data into Excel 2007
I have a workbook with several sheets with a query attached which prompts for a parameter. Each shhet gets a different value for the one parameter. My problem is, when I run the query, no matter which sheet I'm on, the data gets returned to sheet one. What am I doing wrong ? ...

How far ahead of due date?
Hi all: The answer to this question probably varies depending upon one's banking institution but I'll ask the question anyway... :-) Typically how far ahead of the actual due date of bill should we schedule payment of that bill in Money when paying online using a bill pay service? Thanks -Mike You answered your own question. Depends on the FI. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Mike" <Mike@di...

Problem with Importing .csv into Excel
Hi, I have a csv file with a number of words (probably 1000-2000) separated by the "|" character. I wanted to import this file into Excel (Office 2003 Excel), so that I could get each word on a separate line so I could paste them into a blank text document. The problem is, when I go to import the .csv file, Excel imports each word into a column heading, & seeing as how it is limited to 256 columns, my file is not fully imported. I see that Excel has a 65000 row limit, which is more then enough, but I cannot see a way to get Excel to import each word as a separate row, rather tha...

Excel 2002 crashing when OpenDocumentsReadWriteWhileBrowsing enabled
I have an issue with Excel 2002. Its patched up to SP2 with the security hotfixes. When OpenDocumentsReadWriteWhileBrowsing is set to 1 and a I attempt to open an excel file hosted on an Apache TomCat web server, Excel hangs and in turn makes the IE window freeze up. When I set this option to 0 the problem goes away. It appears to only affect this combination of web server and client settings. Has anyone else experienced this, or know anything about the issue? Mike ...