|
Date compatibility error
Hi All, I was hoping I could get some help with my problem, as i have tried everything i know. I have this spreadsheet (attached) and when i type the date in my formula doesnt want to find the corresponding data. But when the dates are copied from the data table and pasted in (same dates) it finds the data fine. I have tried formatting the dates in the same way but with no change. NB: If possible i would like to keep the formatting on the data table without changing as this is a direct data import from another program. If this is the only possibility, let me know and I will try and fix it in a macro (feel free for suggestions) Thank you! Sam +-------------------------------------------------------------------+ |Filename: TEST CLAIM DATES ISSUE.zip | |Download: http://www.excelforum.com/attachment.php?postid=3803 | +-------------------------------------------------------------------+ -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=466607 |
Hey, This seemed to work well, until i went into the sheet, and it it changing dates around as it is seeing them as US dates. For example the 6/3/2005 (in text Aust date) will turn into 3/6/2005 (date format aust date). Is there anyway to tell it to stop swapping around the months where the day numbers are low (1 - 12). Thank you. -------------- Sandy Mann Wrote: Sam, The 'Dates' in the ANC-NSW sheet are text not dates Change them to real dates (with the same formatting as you originally had) by copying a blank cell, highlighting the dates and then selecting Paste Special Add. -- HTH -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=466607 |
Sam,
Being as you want to keep the format in the ANC-NSW sheet as they are, why not just change the dates in the Claim sheet to text? You will have to ensure that you enter the "text dates" in the Claim sheet exactly as they appear in the other sheet. -- HTH Sandy Replace@mailinator with @tiscali.co.uk "Sami82" wrote in message ... Hey, This seemed to work well, until i went into the sheet, and it it changing dates around as it is seeing them as US dates. For example the 6/3/2005 (in text Aust date) will turn into 3/6/2005 (date format aust date). Is there anyway to tell it to stop swapping around the months where the day numbers are low (1 - 12). Thank you. -------------- Sandy Mann Wrote: Sam, The 'Dates' in the ANC-NSW sheet are text not dates Change them to real dates (with the same formatting as you originally had) by copying a blank cell, highlighting the dates and then selecting Paste Special Add. -- HTH -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=466607 |
Hey, Only problem with that is I also have a date / weeks macro based on that input so they need to be treated as dates. -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=466607 |
This seemed to work well, until i went into the sheet, and it it
changing dates around as it is seeing them as US dates. The cell should take up the format of the copied cell when the Paste Special is activated, at least it does with me. Make sure that the cell that you are copying is formatted the way that you want it to be. -- HTH Sandy Replace@mailinator with @tiscali.co.uk |
Hi All, Thank you so much for your help, I worked out that by selecting the date format with the * it doesnt swap the dates around. :) -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=466607 |
Your Welcome. Glad you got it working.
-- Regards Sandy Replace@mailinator with @tiscali.co.uk "Sami82" wrote in message ... Hi All, Thank you so much for your help, I worked out that by selecting the date format with the * it doesnt swap the dates around. :) -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=466607 |
| All times are GMT +1. The time now is 01:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com