Keep date as a number

cindygo · Dec 6, 2010

Hi,

I have a spreadsheet where one column requires a unique number. We currently enter “TodaysDate_1” in the first row and then fill series all rows with its unique number.

For Example
<table class="MsoNormalTable" style="width: 76pt; margin-left: 4.65pt; border-collapse: collapse;" width="101" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15pt;"> <td style="width: 76pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="101" nowrap="nowrap"> 12062010_1
</td> </tr> <tr style="height: 15pt;"> <td style="width: 76pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="101" nowrap="nowrap"> 12062010_2
</td> </tr> <tr style="height: 15pt;"> <td style="width: 76pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="101" nowrap="nowrap"> 12062010_3
</td> </tr> <tr style="height: 15pt;"> <td style="width: 76pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="101" nowrap="nowrap"> 12062010_4
</td> </tr> <tr style="height: 15pt;"> <td style="width: 76pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="101" nowrap="nowrap"> 12062010_5
</td> </tr> <tr style="height: 15pt;"> <td style="width: 76pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="101" nowrap="nowrap"> 12062010_6
</td> </tr> </tbody></table>
I would like a formula to do this for me however when I use =Today() I am returned 40518. Is there a function that will keep the date as a number or text?

thanks for your help,
Cindy

MrKowz · Dec 6, 2010

Try:

=TEXT(TODAY(),"yyyyddmm")

cindygo · Dec 6, 2010

ok, that was too easy! Thanks MrKowz for your quick response.

T. Valko · Dec 6, 2010

cindygo said:
Hi,

I have a spreadsheet where one column requires a unique number. We currently enter “TodaysDate_1” in the first row and then fill series all rows with its unique number.

For Example
<TABLE class=MsoNormalTable style="MARGIN-LEFT: 4.65pt; WIDTH: 76pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=101 border=0><TBODY><TR style="HEIGHT: 15pt"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 76pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=101>12062010_1

</TD></TR><TR style="HEIGHT: 15pt"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 76pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=101>12062010_2

</TD></TR><TR style="HEIGHT: 15pt"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 76pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=101>12062010_3

</TD></TR><TR style="HEIGHT: 15pt"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 76pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=101>12062010_4

</TD></TR><TR style="HEIGHT: 15pt"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 76pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=101>12062010_5

</TD></TR><TR style="HEIGHT: 15pt"><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 76pt; PADDING-TOP: 0in; HEIGHT: 15pt" vAlign=bottom noWrap width=101>12062010_6

</TD></TR></TBODY></TABLE>
I would like a formula to do this for me however when I use =Today() I am returned 40518. Is there a function that will keep the date as a number or text?

thanks for your help,
Cindy

Try this...
=TEXT(NOW(),"mmddyyyy")&"_"&ROWS(A$1:A1)

cindygo · Dec 6, 2010

perfect! thanks guys!

cindygo · Dec 7, 2010

I have a question. Everything is working fine however I noticed that when I add this to a macro the code is changed

from =TEXT(NOW(),"mmddyyyy")&"_"&ROWS(A$1:A1)
to .FormulaR1C1 = "=TEXT(NOW(),""mmddyyyy"")&""_""&ROWS(R1C[-28]:R[-3]C[-28])"

Why is that?

Here is the macro.

Columns("AD:AD").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
With Range("AC4:AC" & LR)
.FormulaR1C1 = "=TEXT(NOW(),""mmddyyyy"")&""_""&ROWS(R1C[-28]:R[-3]C[-28])"
.Copy
.Offset(0, 1).PasteSpecial xlPasteValues
End With

Range("AC1:AC3").Select
Application.CutCopyMode = False
Selection.Copy
Range("AD1:AD3").Select
ActiveSheet.Paste
Columns("AC:AC").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

shg · Dec 7, 2010

Like every new-born, recorded macros always need some clean-up before you show them to the relatives.

Code:

    Columns("AD").Insert
    With Range("AC4:AC" & LR)
        .Formula = "=TEXT(NOW(),""mmddyyyy_"") & ROWS(A$1:A1)"
        .Offset(, 1).Value = .Value
    End With
    Range("AC1:AC3").Copy Destination:=Range("AD1:AD3")
    Columns("AC").Delete

Keep date as a number

cindygo

New Member

Excel Facts

MrKowz

Well-known Member

cindygo

New Member

T. Valko

Well-known Member

cindygo

New Member

cindygo

New Member

shg

MrExcel MVP

Similar threads

Forum statistics

Share this page

Keep date as a number

cindygo

New Member

Excel Facts

MrKowz

Well-known Member

cindygo

New Member

T. Valko

Well-known Member

cindygo

New Member

cindygo

New Member

shg

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock