REPOST: Date format pre-1900.

Hi,

I have a table of immigration ships that have arrived in Australian shores.

Some....okay most.....of the ships arrived before 1900.

I was trying to sort by the arrival date, which is in the format of
DD MMM YYYY, but that didn't work, as there were some 1900 dates.

Tried to re-style the column to DD/MM/YYYY, but the pre-1900 dates
don't change to the new style.

Any suggestions on how to utilise the pre-1900 dates, so that Excel can
recognise them.

Cheers in advance.
Craig.
P.S. Originally sent on the 15/02/2005 - not sure if someone knowledgable
missed it in the other
1000s of messages sent every day.  Cheers.


0
crb (7)
2/17/2005 10:37:27 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
685 Views

Similar Articles

[PageSpeed] 3

Maybe you could enter all your dates as text:

'1882/11/23

Including dates since 1900.

You could even convert your real dates to Text with a formula like:

=TEXT(A1,"yyyy/mm/dd")

Anyway you do it, you'll want to be sorting text (not real dates) so that they
sort correctly.



"Craig & Co." wrote:
> 
> Hi,
> 
> I have a table of immigration ships that have arrived in Australian shores.
> 
> Some....okay most.....of the ships arrived before 1900.
> 
> I was trying to sort by the arrival date, which is in the format of
> DD MMM YYYY, but that didn't work, as there were some 1900 dates.
> 
> Tried to re-style the column to DD/MM/YYYY, but the pre-1900 dates
> don't change to the new style.
> 
> Any suggestions on how to utilise the pre-1900 dates, so that Excel can
> recognise them.
> 
> Cheers in advance.
> Craig.
> P.S. Originally sent on the 15/02/2005 - not sure if someone knowledgable
> missed it in the other
> 1000s of messages sent every day.  Cheers.

-- 

Dave Peterson
0
ec357201 (5290)
2/18/2005 2:10:38 AM
Hi Craig,
Excel does not recognize any date before 1900, but VBA does.
Keep those dates as text.

You can use John Walkenbach's  Extended Dates routines
to work with them arithmetically, but be aware that there are
problems as calendar changes --  I believe that is at least
mentioned on his site.
   Extended Date Functions Add-In
   http://www.j-walk.com/ss/excel/files/xdate.htm

More information on Date and Time
     http://www.mvps.org/dmcritchie/excel/datetime.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Craig & Co." <crb@amsa.gov.au> wrote in message news:42151ca3$0$57503
> I have a table of immigration ships that have arrived in Australian shores.
> Some....okay most.....of the ships arrived before 1900.


0
dmcritchie (2586)
2/28/2005 9:14:19 PM
Reply:

Similar Artilces:

auto format cells to highlight only when data is entered
Is there a way to automatically have cells highlight when data is entered. I have a column that only receives occasional data and I would like it to stand out. I'm very new to excel. Thanks Try "Conditional Formatting:. Select the cells in question, then: <Format> <ConditionalFormat>, First window - "Cell value is" second window - "Not equal to" Third window, two double quotes - "" Choose your formats, then <OK>. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may ...

POPULATING DATES IN A CALENDAR
I have 7 worksheets with a list of trucks from different plants that need painting throughout the year. I wish to enter a date next to the truck number in one worksheet and then have that date be colored (or better yet have the truck number appear) in a 12 row 31 column calendar in another worksheet. My goal is to quickly see which dates I have open to schedule our paint shop to paint a truck. Thanks in advance for any help. ...

Conditional Formatting for Changes to cells
I want to use either conditional formatting or change tracking to change the font color on any changes made to a spreadsheet by a user. Just one user will be using each spreadsheet but the changes are then submitted to someone for data entry into the big iron. Thanks dick Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.Color = vbRed End Sub Bob Umlas Excel MVP "Dick Stapleton" <dick.stapleton@coldist.com> wrote in message news:uxK7%23IqvJHA.4928@TK2MSFTNGP03.phx.gbl... >I want to u...

repost; Automatically updating formulae in multiple sheets
I list sales of 20+ products each week for the year A2-A23 are the products and B-BA are the colums for each week I have multiple sheets that have the same sales results in the same format foreach previous year 2004;2003;2002;2001;2000 Each week I want to compare total sales in the year to date i.e weeks 1-20 with weeks 1-20 in each previous year. so next week it will be 1-21. I have on each sheet a cumulative total for each of the 20 products in the year to date i.e Cell A25=SUMB2:BA2 and Cell A26=SUMB3:BA3 and so on for each product. This means each week I have to alter the formula in C...

From and to date
In the report wizard it gives the option to group by date but not a start and end date. Is there a way to do this through the wizard or does the report have to be wriiten manually. The field is date time format Thanks for any help Not sure what you mean by "written manually" ... If you base your report on a query, rather than a table, you can use selection criteria in the query to limit records to a date range. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or d...

Date Format #3
I have a spreadsheet that I want the date to be formatted as day/month/year (13-09-03). I go to format cells and choose that format for my column. When I type in the date as 13/09/03 it will not convert the date to the correct format. However if I type in 09/13/03 Excel will convert to the proper format. Does this have anything to do with my regional settings in Windows. Would I have to change the setting to day/month/year in windows. I am using Excel 2000. Thanks in advance for your help. Regards, Dee Number format affects display only, not how XL parses an entry. To change the p...

Repost
I sent his message earlier but have not been able to view it from here so I apologize if it has already been answered. I had heard that the refresh was released sometime last week but have received no notification from MS about it. When will I know? Will MS send an email to everyone that purchased the Beta 2 Kit? I have clicked "Check for Updates" but there is no information there. All I get is "All your office products are up to date". Thanks for reading. Hi Robin, I have the following URL to view, nothing more. http://officebeta.microsoft.com/home/default.aspx I rea...

Formatting of Data Labels in Histograms
I have used histograms extensively in MS2003 but have encountered a broblem using MS2007. I have a spreadsheet that shows dollar amounts of contracts being awarded during certain months over a three year period. Some months more than one contract is awarded, others have one or none. Therefore, my histogram is a stacked column type. My X-axis shows months and my Y-axis dollars. The problem I am encountering is with the data labels which I put centered on the column. The dollar amounts show correctly but for some reason there is a zero at each interval of the X-axis, even for months...

sequential date
Is it possible to have in sequence a date value I require four dates the same before sequencing the next date, so 01/01/11 01/01/11 01/01/11 01/01/11 02/01/11 02/01/11 02/01/11 02/01/11 03/01/11 03/01/11 03/01/11 03/01/11 etc etc Cheers In A1 : initial date In A2 : =IF(COUNTIF($A$1:A1,A1)<4,A1,A1+1) copy down as needed. Daniel > Is it possible to have in sequence a date value > > I require four dates the same before sequencing the next date, so > > 01/01/11 > 01/01/11 > 01/01/11 > 01/01/11 > 02/01/11 > 02/01/11 > 02/01/11 > 02/01/11 > 03/01/1...

undoing conditional formatting
Hello and great day today. I use conditional formatting to find an error(s) in many rows and columns when my grand totals aren't matching both down and across. I might have 15 columns and 45 rows with data and currency listed. I must make sure each row across matches to a grand total and same down with the columns. The conditional formatting is great for high-liting discrepancies But it also highlites rows that are not really errors when put together with another row. So I fix the row with an actual error but how do I get rid of the highlite on the rows which are not really errors. I can...

attempting to format a color pattern in tab 3, based tab 2 and tab
I have many cells of data on tab 1 (Actual data), it is replicated on tab 2 but with a different data set (Budget data), and tab 3 is the difference between those first 2 tabs and I want each of the cells on tab 3 to evaluate against tabs 1 and 2. In tab 3, if the same cell, say c3, in tab 1 is less than ABS of 1, AND the same cell in tab 2 is greater than ABS of 1, then the same cell, say C3, on tab 3 I want a color pattern of Blue, else no color pattern. Thanks for your help. Dear Muturboman, You can do this with Conditional Formatting ("Use a formula to determine ...

Counting dates within a range
I have a large database of ~10,000 dated and timed entries. Each entry represents the date and time of a hospital admission. I would like to know for each entry, how many other events took place within 24 hours. I am trying to figure out how many hospital admission happened in the 12 hours prior to and after each admission to estimate the daily admission rate more accurately than just by calendar day. I have tried =COUNTIFS(AV:AV,"<="&(AV2+0.5),AV:AV,">="&(AV2-0.5)) Where AV is the column of all dates, and AV2 is the date in question. This fo...

Date Intervals from dates
Hi, I've created a query that lists the dates between a start date and an end date. From the list of dates, i need to find the intervals between the dates and then find the average of that. How do I get the intervals in a column in the query. thanks heaps See: Subquery basics: Get the value in another record at: http://allenbrowne.com/subquery-01.html#AnotherRecord The example is for the previous meter reading so you can subtract from the current value, but it works the same for dates. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http:/...

How do I change the comment default format to include date?
In Excel 2007, I want to change the default format for comments, so that the date the comment was added is included in the comment as well as the name of the user who added the comment. Don't think that you can, the username is all you get, the rest you add yourself. -- HTH Bob "frj5000" <frj5000@discussions.microsoft.com> wrote in message news:06C2615D-AA43-4A1B-B6A3-AF551467BEE6@microsoft.com... > In Excel 2007, I want to change the default format for comments, so that > the > date the comment was added is included in the comment as wel...

Grouping with Outlook 2003; Conversation ordering, dates
We're (just now) moving to Outlook 2003 at my business and thus I can no longer use Thunderbird that I've been using for quite a while. Outlook's grouping features seem convenient for the most part, but I can't seem to duplicate the threaded message behavior that Thunderbird has. I can group Outlook messages by Conversation, but only in ascending/decending order, and unfortunately this is in alphabetical order. I want to keep my messages in conversation grouping, but want the most recently updated conversation at the top of my list. The way I have it figured out so far, if a...

Hide timeline date
I'd like to hide the text of the date that appears in the text above the timeline icon. I want the text to appear, but with so many events on my timeline I want to reduce the clutter. How can I do this? On Fri, 22 May 2009 09:19:00 -0700, MikeM <MikeM@discussions.microsoft.com> wrote: >I'd like to hide the text of the date that appears in the text above the >timeline icon. I want the text to appear, but with so many events on my >timeline I want to reduce the clutter. How can I do this? Icon? Do you mean the milestone shapes? If so then I've put some edited ve...

showing dates, e.g. 1st, 2nd, 3rd with st, nd, rd in superscript
I've recently started using Word 2007. I prefer to write dates in the form 1st January, rather than January 1. My previous edition of word always automatically gave st, nd and rd as smaller font superscripts or suffixes. I can't find a way of getting Word 7 to do that as a default. See the Date Fields with Ordinals section of the following page of fellow MVP Graham Mayor's website: http://www.gmayor.com/formatting_word_fields.htm -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid p...

Date field
I have a date field which has a range of dates from 1850 - 1930 Each entry has dd mm yyyy how do I 1) format all fields to read e.g. dd Mmm yyyy 2) sort successfully on all records Bob M Select the range you want to format. Right click. In the Number Tab you will find the field "date" and on the right side you will find the format you need. Hope it helps. "Bob Matthews" wrote: > I have a date field which has a range of dates from 1850 - 1930 > Each entry has dd mm yyyy > how do I > 1) format all fields to read e.g. dd Mmm yyyy > 2) sort successfully ...

Wrong Date
Hello, I dont know if anyone has asked this question before as i am new to this. I have Outlook 2000 and when I receive an email it is showing the wrong time by an hour but the date is correct, can someone please help me to ressurect this ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ "forro5" <forro5.100ce0@outlookforum.com> wrote in message news:forro5.100ce0@outlookforum.com... > > Hello, > > I dont know if anyone has asked this quest...

Office Outlooks dont show embeded images in HTML format mail
Why does in Outlook Express I see image embeded in html format email and in Office Outlook 2000 or 2003 there is no image only small cross but image is sended with email? Why this email source below look different in Outlook Express and in Outlook with situation described above? From: '=?ISO-8859-2?B?UC5ILlUuIFBvbGNhbiAtIE1hcmxlbmEgSmFub3Rh?=' <laopola@kalaopoa.pd> MIME-Version: 1.0 Content-Type: multipart/related; boundary="X----1897694375454659af00c36" Message-Id: <20061030200202.8B7E937C6B@mail.lalalalal> Date: Mon, 30 Oct 2006 21:02:02 +0100 (CET) This i...

How do I subtract a date from a date for a sum of total years?
I am trying to calculate how many years an employee has worked. I have start date and current date. What is the formula to return years worked? If the current date is in A1 and the start date is in B1, then =year(A1)-year(B1) formatted as general will return the number of years worked. -- Gary''s Student "Paige" wrote: > I am trying to calculate how many years an employee has worked. I have start > date and current date. What is the formula to return years worked? Hi Paige One way =DATEDIF(Startdate,currentdate,"y") for years only =DATEDIF(Start...

Query to get information that doesn't match by date
I have a table named TTIME with columns in it named: Casenum, Tidate, Reason, Snum I have a table named CLIENTSCASENOTES with columns: Casenum, CNdate, CNnote, Snum. the column TIdate, in the TTIME table stores it's dates as mm/dd/yyyy the column CNdate, in the CLIENTSCASENOTES tables stores its dates as mm/dd/yyyy with the time on the end. What I am trying to do is link the TTIME table with the CLIENTSCASENOTES table and then get the following out of the query: All the records from TTIME that do not have a record in the CLIENTSCASENOTES table with date matching the da...

Changing base date?
I was copying data from an opened CSV into a template and noticed that the all of the dates were wrong! I changed the numeric format to general in both windows to see if the base number was the same. They were I then changed the number to 0 then formatted it as a date and saw one spreadsheet format the zero as "1/0/1900" and the other spreadsheet formatted the zero as "1/1/1904" What gives? Thanks. One computer is using the 1904 date system under tools>options>calculations Regards, Peo Sjoblom "Aaron Bratcher" <aaron@adsgainc.com> wrote i...

US-UK date formats & downloading transactions
I have accounts in both the US (=base currency) + UK. I typically download transactions manually from the institutions I hold accounts with, but the US-UK date format difference is causing problems (eg. 01/10/05: in the US = Jan 10th, in the UK = Oct 1st). I can't find a way of dealing with this. Help? In microsoft.public.money, "Anthro girl" <Anthro girl@discussions.microsoft.com> wrote: >I have accounts in both the US (=base currency) + UK. I typically download >transactions manually from the institutions I hold accounts with, but the >US-UK date form...

Conditional Formats
I want to set a set of criteria for a row, within a give date range, in a way that as the 'due' date approaches, the cells change color from green to yellow to red. how can i set this up? You use conditional formatting. Depending on how you calculate your 'due' date, you use a condition like: =a1+30>=today() Regards Fred "lau" <lau@discussions.microsoft.com> wrote in message news:3E3F20E9-12F8-4EA5-B430-DCE4E59A70DE@microsoft.com... >I want to set a set of criteria for a row, within a give date range, in a >way > that as the &...