Date error

[GregR]

I have the following sub which errors:
Sub Monthdate()
Dim celldt As Date
Dim TsMonth As Date
TsMonth = InputBox("Enter month number for timesheet, such as (1) for Jan",
"Timesheet Month...", 1)
celldt = TsMonth & "/1/2005" > Error on this line
Range("S2").Value = celldt
End Sub

I want cell S2 to display the first date of the month specified in the input
box. TIA

Greg

 

[Bob Phillips]

Greg,

You want a number not a date,

Sub Monthdate()
Dim celldt As Date
Dim TsMonth As Integer
TsMonth = InputBox("Enter month number for timesheet, such as (1) for Jan",
"Timesheet Month...", 1)
celldt = TsMonth & "/1/2005" > Error on this line
Range("S2").Value = celldt
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[GregR]

Bob, thanks again, can't believe I missed that one.

Greg

 

[Tom Ogilvy]

Sub Monthdate()
Dim celldt As Date
Dim TsMonth As Date
TsMonth = InputBox("Enter month number for timesheet, such as (1) for Jan",
"Timesheet Month...", 1)
celldt = DateValue(TsMonth & "/1/2005")
Range("S2").Value = celldt
End Sub

or
celldt = DateSerial(2005,clng(TsMonth),1)

 

[Tom Ogilvy]

As Bob pointed out, in addition

Sub Monthdate()
Dim celldt As Date
Dim TsMonth As String
TsMonth = InputBox("Enter month number for timesheet, such as (1) for
Jan", _
"Timesheet Month...", 1)
celldt = DateValue(TsMonth & "/1/2005")
Range("S2").Value = celldt
End Sub

 

[GregR]

Tom, just curious, what is the "clng" in the dateSerial alternative. TIA

Greg

 

[Tom Ogilvy]

It converts the TsMonth variable to a long. I had assumed it was a string
and missed the fact you dim'd it as date.

 

[Curt Finch]

Another option would be to forget about excel timesheets and use
Journyx, which is a free
program to do just about any kind of web timesheet tracking you could
ever think of.
http://journyx.com/clf/gendl.html

 

[sales]

There's also Projux (www.projux.com), which lets you gather your team's
project information online and optionally export it to Excel (as well
as PDF or XML) for custom analysis. Projux is fully web-hosted and is
perfect for small to mid-sized services firms. It can do time & expense
tracking, reporting and invoice generation instantly online.