Date Help

J

JamieD

Hi all,
This one has me beat so I hope someone can help.
I'm using a barcode scanner to collect data (serial number and date
and upload this to excell.
A simple formula then changes the date into a numerical string as th
date can not be recognised by excel (the method of copy/paste and add
blank cell contents does work but not in a macro).
This was all fine until today when the date format seem to be change
by excel from a text string into a numerical value. This would be fin
apart from the date is incorrect (displaying 37990 which is incorrec
as the 01/04/2004 should be 38078,37990 is actually 04/01/2004 ).
I can see the date is being change around but have no idea how to fi
this.
I know that the information is correct on the scanner and also correc
in the software that copies across the information, as I have uploade
to a wordpad document and the information is spot on.

This has been tested on several machines with different worksheets bu
all have the same outcome.

I hope this makes sense and someone can help.

Many Thanks
 
R

Ron Rosenfeld

Hi all,
This one has me beat so I hope someone can help.
I'm using a barcode scanner to collect data (serial number and date)
and upload this to excell.
A simple formula then changes the date into a numerical string as the
date can not be recognised by excel (the method of copy/paste and add a
blank cell contents does work but not in a macro).
This was all fine until today when the date format seem to be changed
by excel from a text string into a numerical value. This would be fine
apart from the date is incorrect (displaying 37990 which is incorrect
as the 01/04/2004 should be 38078,37990 is actually 04/01/2004 ).
I can see the date is being change around but have no idea how to fix
this.
I know that the information is correct on the scanner and also correct
in the software that copies across the information, as I have uploaded
to a wordpad document and the information is spot on.

This has been tested on several machines with different worksheets but
all have the same outcome.

I hope this makes sense and someone can help.

Many Thanks
Click to expand...

It's a little unclear as to exactly what is going on, as the date strings you
post are ambiguous.

However, I would consider that you may have an issue with regional settings
that perhaps was unaccounted for in your original formula, but would not be a
problem until ambiguous dates occurred.

If you could provide more data, and also give examples of the actual input from
the bar code, I'm sure a solution could be devised.


--ron
 
F

Frank Kabel

Hi
you may post your formula and some example data which you got from your
scanner
 
G

Guest

You might have to change the regional settings on your
computer.

Start>Settings>Control Panel.Regional Settings
 
J

JamieD

Sorry all,
That wasn't very clear was it, let me try it again.
I am basically trying to count a daily workload, using the COUNTI
function where the serial number is one on the days workload and th
date matches todays date.
The data is scanned into the barcode which stamps it with the date.
The information is uploaded into 2 columns on Excel as below

B10.1235| 31/03/2004

Up until today this worked fine as I used the formula to produce
numerical value for the date e.g. 38078 for 01/04/03
=(IF(ISERROR(DATEVALUE(F13)),"0",DATEVALUE(F13)))
This formula is only used as Excel cannot recognise the text string o
the date and therefore does not count the information.

However today the information coppied across today is displayed as

B10.1235| 37990

If I change the format of the cell to date it is displayed a
04/01/2004.

I'm not sure how this is going wrong as the reginal settings ar
correct and the date is correct when it is uploaded from the scanner a
I have uploaded it into a word document to test.

I hope this makes more sense now.

Many Thanks
 
F

Frank Kabel

Hi
try preformating the column in whcih you paste your data (question: how
do you paste the scanner data - manually?) with your date format
 
R

Ron Rosenfeld

I'm not sure how this is going wrong as the reginal settings are
correct and the date is correct when it is uploaded from the scanner as
I have uploaded it into a word document to test.
Click to expand...

Regional settings cannot be "incorrect" but they may or may not be the same as
the bar code settings.

So what are your regional settings??? In other words, when you check on the
regional settings (which, in Windows XP, is in the control panel folder), what
language/country do you see, and what is the short and long date displayed
there?

What Excel is doing is interpreting the 01/04/04 according to a US regional
format which is mm/dd/yy so this becomes 1 January 2004 or 37990. However the
bar code is apparently in a non-US setting and you would like that same text
string interpreted as dd/mm/yy. This is outside of Excel.

If you do not want to change your regional settings, then one method of
handling the issue would be to preformat your column as text. You can then use
the following formula to convert it to a date:

=DATE(RIGHT(B1,2),MID(B1,4,2),LEFT(B1,2))






--ron
 
Top