date as number not a text cell

  • Thread starter Thread starter lud1
  • Start date Start date
L

lud1

Hi,
I have problem with date format. I have created file with two sheets
Data are tranfered from one sheet to the other by macro. The cell H3 i
the first sheet as well as the columne C in the second sheet ar
formated as a date dd-mm-yyyy (format>cell>date). In fact at the firs
glace everything looks OK, however although the above format ofth
cells they are treated as a text not a number calculating days sinc
1900-Jan-0. How in easy way to make them numbers. Regional setting i
my computer is yyyy-mm-dd, but the file will be filled on by severa
people from defferent computers with different settings.
Visible date format in the first sheet should be dd-mm-yyyy, but th
format in the second sheet simple must be a number (to enable transfe
it further), but there is no request for visible format of C column.
I have reviewed all info regarding Dates and Times in Excel created b
Chip Pearson http://cpearson.com/excel/datetime.htm
and tried to use his special macro for date entry, unfortunatel
without success.
Please help me to solve the problem by special macro or setting - don'
know. if macro where it shoud be located.
Thanks for quick and simple reply
lud
 
Hi

Excel's standard date system starts at 01.January 1900. All earlyer dates
would be negative, and Excel doesn't recognize them as dates - so p.e.
01-01-1880, entered into cell formatted as date, is interpreted as text.
You can try the 1904 date system (Options.Calcualation and check checkbox),
which allows negative data, but as I never have used it myself, you have to
find out yourself about it.
 
Thanks Arvi,
but my dates are not as old as you suggested, they are current of 2003
and 2004.
Any other suggestions ?

Arvi said:
*Hi

Excel's standard date system starts at 01.January 1900. All earlyer
dates
would be negative, and Excel doesn't recognize them as dates - so
p.e.
01-01-1880, entered into cell formatted as date, is interpreted as
text.
You can try the 1904 date system (Options.Calcualation and check
checkbox),
which allows negative data, but as I never have used it myself, you
have to
find out yourself about it.
Click to expand...
of 2003 and 2004. Any suggestions ?
 
Hi


lud1 > said:
but my dates are not as old as you suggested, they are current of 2003
and 2004.
Click to expand...
?
But on second thought, I'm not sure I translated it correctly myself! And so
it was (checked withy dictionary)!

Are you sure fields in second sheet are formatted as Date's or as General
before values from first sheet are transfered.
And maybe you post your macro too - when cell formattings are correct, then
the problem must lie there obviously.
 
Try using a formula such as =ISTEXT(A1), copied down, to see if each of your
original 'dates' is actually text.
 
Post your macro.

I just ran a simple test and it worked fine, so I guess like Arvi, that it
is in the macro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob said:
*Post your macro.

I just ran a simple test and it worked fine, so I guess like Arvi
that it
is in the macro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...

Bob,
This is part of my macro regarding data trasfer.

'Adding Rows
Sheets("B").Select
Rows("2:21").Select
Selection.Insert Shift:=xlDown
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = False
'Transfer of the first line
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Proforma!R3C8"
'Transfer of the further rows
Range("A2:Ah2").Select
Selection.AutoFill Destination:=Range("A2:Ah21")
Type:=xlFillDefault
Range("A2:Ah21").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=22, Criteria1:="0"
Rows("2:5000").Select
Range("Q5").Activate
Selection.Delete Shift:=xlUp
Range("v1").Select
Selection.AutoFilte
 
Back
Top