Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Date in correct cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Date in correct cell
Hi,
You can try datepart function WLMPilot wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Date in correct cell
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 "Equiangular" wrote: Hi, You can try datepart function WLMPilot wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Date in correct cell
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 "Equiangular" wrote: Hi, You can try datepart function WLMPilot wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Date in correct cell
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 "WLMPilot" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date(Year,Month,Day) not returnign correct date | Excel Discussion (Misc queries) | |||
Macro to insert at correct point | Excel Programming | |||
date not correct in cell | Setting up and Configuration of Excel | |||
formatting a cell to display the correct date. | Excel Discussion (Misc queries) | |||
How do I insert pictures in excel with the correct aspect ratio? | Excel Discussion (Misc queries) |