Date compatibility error

[Sami82]

Hi All,

I was hoping I could get some help with my problem, as i have trie
everything i know.

I have this spreadsheet (attached) and when i type the date in m
formula doesnt want to find the corresponding data. But when the date
are copied from the data table and pasted in (same dates) it finds th
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 tabl
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 i
in a macro (feel free for suggestions)

Thank you!
Sa

+-------------------------------------------------------------------
|Filename: TEST CLAIM DATES ISSUE.zip
|Download: http://www.excelforum.com/attachment.php?postid=3803
+-------------------------------------------------------------------

 

[Sandy Mann]

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

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Sami82]

Hey,

This seemed to work well, until i went into the sheet, and it i
changing dates around as it is seeing them as US dates. For example th
6/3/2005 (in text Aust date) will turn into 3/6/2005 (date format aus
date). Is there anyway to tell it to stop swapping around the month
where the day numbers are low (1 - 12).

Thank you.

--------------

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Sami82]

Hey,

Only problem with that is I also have a date / weeks macro based o
that input so they need to be treated as dates

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Sami82]

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.

 

[Sandy Mann]

Your Welcome. Glad you got it working.

--
Regards

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk