Insert Date in correct cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WLMPilot - 31 Dec 2007 11:12 GMT

I have a sheet (PAS) that has a type of calender to track the number of hours
I work at part-time job. Here is the set up of calender:

B125:B176 = date for beginning of week (which is a Monday) (52 rows ie weeks)
C125:G176 = columns for Mon Tue Wed Thur Fri for 52 weeks
where
B125 C125............G125
12/17/07 Mon Tue.......Fri

I would like a userform that accepts the following:
Date worked
Clock In
Clock Out

I would like to know is how to take the date worked and find the spot it is
suppsed to place total hours in. Example: If I worked on 12/27/07, the
macro needs to figure out the week it falls in and then figure out where to
place it (ie Thursday).
In this example, total hours needs to go in F126.

B125 = 12/17/07
B126 = 12/24/07 C126=Mon D126=Tue .....F126=Thur G126=Fri
B127 = 12/31/07

Thanks,
Les

Reply to this Message

Equiangular - 31 Dec 2007 15:07 GMT

Hi,

You can try datepart function

> I have a sheet (PAS) that has a type of calender to track the number of hours
> I work at part-time job. Here is the set up of calender:
[quoted text clipped - 22 lines]
> Thanks,
> Les

Reply to this Message

WLMPilot - 31 Dec 2007 16:02 GMT

I don't believe that is exactly what I am looking for. When I enter a date
via userform, I need the code to scan B125:B176 (cells that contain the date
for the beginning of the workweek ie Mondays). During the "scan", the code
will determine the appropriate row and column. Example: Date entered (I'll
name variable as datework) is 12/17/07. After pressing ENTER, code
determines that datework > B126 and datework < B127. Now I will simply
substract (datework - B126) to determine the column, which would be 3. The
columns for Mon - Fri are C-G.
Therefore, the OFFSET from B126 would be (datework-B126)+1.

The equivalent Excel Function for what I am looking for is
MATCH(datework,B125:B176,1)

Les

> Hi,
>
[quoted text clipped - 26 lines]
> > Thanks,
> > Les

Reply to this Message

Joel - 31 Dec 2007 16:04 GMT

here is a macro that works

Sub GetHours()
MyDate = DateValue("3/5/08")
RowCount = 125
Do While RowCount <= 176

If MyDate < Range("B" & (RowCount + 1)) Then
Exit Do
End If

RowCount = RowCount + 1
Loop
Coloffset = Weekday(MyDate, vbMonday)
hours = Range("B" & (RowCount)).Offset(0, Coloffset)
End Sub

> Hi,
>
[quoted text clipped - 26 lines]
> > Thanks,
> > Les

Reply to this Message

Mike Fogleman - 31 Dec 2007 23:07 GMT

A userform with 3 textboxes and a CommandButton:
In the UserForm code module:
Option Explicit

Private Sub CommandButton1_Click()

If IsDate(Me.TextBox1.Text) Then
MyDate = CDate(Me.TextBox1.Text)
Else
MsgBox "Enter a valid date"
Me.TextBox1.Text = ""
End If
WkDay = WorksheetFunction.Weekday(MyDate)
If WkDay = 1 Or WkDay = 7 Then
MsgBox ("Cannot be a Saturday or Sunday")
Me.TextBox1.Text = ""
End If
If IsDate(Me.TextBox2.Text) Then
CkIn = CDate(Me.TextBox2.Text)
Else
MsgBox "Enter a valid time"
Me.TextBox2.Text = ""
End If

If IsDate(Me.TextBox3.Text) Then
CkOut = CDate(Me.TextBox3.Text)
Else
MsgBox "Enter a valid time"
Me.TextBox3.Text = ""
End If
UserForm1.Hide
CalcEnterHours
End Sub

In a standard code module:

Option Explicit
Public MyDate As Date
Public CkIn As Date, CkOut As Date
Public MyHours As Integer, WkDay As Integer

Sub CalcEnterHours()
Dim c As Range

If WkDay = 1 Or WkDay = 7 Then GoTo reset
MyDate = (MyDate - WkDay) + 2
Set c = Cells.Find(MyDate)
MyHours = Hour(CkOut) - Hour(CkIn)
c.Offset(0, WkDay - 1).Value = MyHours
Unload UserForm1
Exit Sub
reset:
UserForm1.Show
End Sub

PS. If you have an email address I can send it to you.
Mike F

>I have a sheet (PAS) that has a type of calender to track the number of
>hours
[quoted text clipped - 26 lines]
> Thanks,
> Les

Reply to this Message