Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Produce the most recent date
I have a stock sheet in which i keep track of the date that stock was
received, where the stock is stored and the total stock. I enter the date that the stock was received and the date that it was entered into the storage place. What i need is for the Total Stock sheet to automatically calculate the most recent date the stock was received. Basically i want to use a function to compare two dates and produce the most recent one, but am not sure which function will do this as the one that i know only do this for numbers in which the smallest or earliest figure is obvious... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Produce the most recent date
The MAX() function will return the most recent date:
A1 = 2/16/2007 A2 = 2/12/2007 =MAX(A1:A2) Returns: 2/16/2007 If it returns something like 39129 just format as DATE. Biff "Julie" wrote in message ... I have a stock sheet in which i keep track of the date that stock was received, where the stock is stored and the total stock. I enter the date that the stock was received and the date that it was entered into the storage place. What i need is for the Total Stock sheet to automatically calculate the most recent date the stock was received. Basically i want to use a function to compare two dates and produce the most recent one, but am not sure which function will do this as the one that i know only do this for numbers in which the smallest or earliest figure is obvious... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Produce the most recent date
This still produces the wrong date. For instance 23/1/07 is given instead of
16/2/07 because it is a "larger" "T. Valko" wrote: The MAX() function will return the most recent date: A1 = 2/16/2007 A2 = 2/12/2007 =MAX(A1:A2) Returns: 2/16/2007 If it returns something like 39129 just format as DATE. Biff "Julie" wrote in message ... I have a stock sheet in which i keep track of the date that stock was received, where the stock is stored and the total stock. I enter the date that the stock was received and the date that it was entered into the storage place. What i need is for the Total Stock sheet to automatically calculate the most recent date the stock was received. Basically i want to use a function to compare two dates and produce the most recent one, but am not sure which function will do this as the one that i know only do this for numbers in which the smallest or earliest figure is obvious... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Produce the most recent date
In which case you haven't got dates in your spreadsheet, you've got text
strings. Change them to dates. One possibility is that you are using a different date format from that which your Excel settings or your Windows rehgional settings are expecting. Another possibility is that you've got spare spaces or other non-printing characters in your text string. Another possibility is that the cell was formatted as text before you put the data in. -- David Biddulph "Julie" wrote in message ... This still produces the wrong date. For instance 23/1/07 is given instead of 16/2/07 because it is a "larger" "T. Valko" wrote: The MAX() function will return the most recent date: A1 = 2/16/2007 A2 = 2/12/2007 =MAX(A1:A2) Returns: 2/16/2007 If it returns something like 39129 just format as DATE. Biff "Julie" wrote in message ... I have a stock sheet in which i keep track of the date that stock was received, where the stock is stored and the total stock. I enter the date that the stock was received and the date that it was entered into the storage place. What i need is for the Total Stock sheet to automatically calculate the most recent date the stock was received. Basically i want to use a function to compare two dates and produce the most recent one, but am not sure which function will do this as the one that i know only do this for numbers in which the smallest or earliest figure is obvious... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Produce the most recent date
I am Australian and need the dates in the Australian format (dd,mm,yyyy).
Excel is set up to accept the Australian date format, and yes i do have the format for the cells set to date. Is there no way to produce the most recent date if it is not the US format????? "David Biddulph" wrote: In which case you haven't got dates in your spreadsheet, you've got text strings. Change them to dates. One possibility is that you are using a different date format from that which your Excel settings or your Windows rehgional settings are expecting. Another possibility is that you've got spare spaces or other non-printing characters in your text string. Another possibility is that the cell was formatted as text before you put the data in. -- David Biddulph "Julie" wrote in message ... This still produces the wrong date. For instance 23/1/07 is given instead of 16/2/07 because it is a "larger" "T. Valko" wrote: The MAX() function will return the most recent date: A1 = 2/16/2007 A2 = 2/12/2007 =MAX(A1:A2) Returns: 2/16/2007 If it returns something like 39129 just format as DATE. Biff "Julie" wrote in message ... I have a stock sheet in which i keep track of the date that stock was received, where the stock is stored and the total stock. I enter the date that the stock was received and the date that it was entered into the storage place. What i need is for the Total Stock sheet to automatically calculate the most recent date the stock was received. Basically i want to use a function to compare two dates and produce the most recent one, but am not sure which function will do this as the one that i know only do this for numbers in which the smallest or earliest figure is obvious... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Produce the most recent date
I use UK dates, and MAX works fine with them. Dates are only numbers
to Excel, so if your dates are proper dates then it should work ok. As a check, select one of your date cells and format it as a number - if it is a proper date then you should get something around 39000 showing. Format it back to date and try the same on the other date cell. Hope this helps. Pete On Feb 21, 10:57 pm, Julie wrote: I am Australian and need the dates in the Australian format (dd,mm,yyyy). Excel is set up to accept the Australian date format, and yes i do have the format for the cells set to date. Is there no way to produce the most recent date if it is not the US format????? "David Biddulph" wrote: In which case you haven't got dates in your spreadsheet, you've got text strings. Change them to dates. One possibility is that you are using a different date format from that which your Excel settings or your Windows rehgional settings are expecting. Another possibility is that you've got spare spaces or other non-printing characters in your text string. Another possibility is that the cell was formatted as text before you put the data in. -- David Biddulph "Julie" wrote in message ... This still produces the wrong date. For instance 23/1/07 is given instead of 16/2/07 because it is a "larger" "T. Valko" wrote: The MAX() function will return the most recent date: A1 = 2/16/2007 A2 = 2/12/2007 =MAX(A1:A2) Returns: 2/16/2007 If it returns something like 39129 just format as DATE. Biff "Julie" wrote in message ... I have a stock sheet in which i keep track of the date that stock was received, where the stock is stored and the total stock. I enter the date that the stock was received and the date that it was entered into the storage place. What i need is for the Total Stock sheet to automatically calculate the most recent date the stock was received. Basically i want to use a function to compare two dates and produce the most recent one, but am not sure which function will do this as the one that i know only do this for numbers in which the smallest or earliest figure is obvious...- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Produce the most recent date
Excel will cope happily in Australian format (and that's the same as we have
here in England), so I guess that the problem is probably one of the things I suggested earlier. For a date in cell A1 to sort, =ISNUMBER(A1) should be TRUE and =ISTEXT(A1) should be FALSE. If the cells are text, check again for spaces or other non-printing characters before or after the number. To convert the text to dates, you could try Data/ Text to columns/ delimited/ out to last stage/ select Column data format: Date: DMY Other options you could try are to put a zero in a spare cell, copy, and Edit/ Paste special/ add (or a 1 and Edit/ Paste special/ multiply). -- David Biddulph "Julie" wrote in message ... I am Australian and need the dates in the Australian format (dd,mm,yyyy). Excel is set up to accept the Australian date format, and yes i do have the format for the cells set to date. Is there no way to produce the most recent date if it is not the US format????? "David Biddulph" wrote: In which case you haven't got dates in your spreadsheet, you've got text strings. Change them to dates. One possibility is that you are using a different date format from that which your Excel settings or your Windows rehgional settings are expecting. Another possibility is that you've got spare spaces or other non-printing characters in your text string. Another possibility is that the cell was formatted as text before you put the data in. "Julie" wrote in message ... This still produces the wrong date. For instance 23/1/07 is given instead of 16/2/07 because it is a "larger" "T. Valko" wrote: The MAX() function will return the most recent date: A1 = 2/16/2007 A2 = 2/12/2007 =MAX(A1:A2) Returns: 2/16/2007 If it returns something like 39129 just format as DATE. Biff "Julie" wrote in message ... I have a stock sheet in which i keep track of the date that stock was received, where the stock is stored and the total stock. I enter the date that the stock was received and the date that it was entered into the storage place. What i need is for the Total Stock sheet to automatically calculate the most recent date the stock was received. Basically i want to use a function to compare two dates and produce the most recent one, but am not sure which function will do this as the one that i know only do this for numbers in which the smallest or earliest figure is obvious... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
most recent date (correction) | Excel Discussion (Misc queries) | |||
most recent date | Excel Discussion (Misc queries) | |||
keep rows with most recent date | Excel Discussion (Misc queries) | |||
Looking up the most recent date | Excel Worksheet Functions | |||
produce a formulate to produce assigned seats for dinner | Excel Worksheet Functions |