I am trying to put together a userform based time card calculator. User inputs the time in the time out and how long of a lunch. Then the program will display total hours worked for the day. Ive attached what I have so far. The only thing that is not very clear is that I have one hidden textbox for the lunch. It is there only for calculating and the visible one is going to inc by :15 min.
I am trying to put together a userform based time card calculator. User inputs the time in the time out and how long of a lunch. Then the program will display total hours worked for the day. Ive attached what I have so far. The only thing that is not very clear is that I have one hidden textbox for the lunch. It is there only for calculating and the visible one is going to inc by :15 min.
I have a time card that is split into sections so there are a lot of SUM formulas. However, I need to include a space for breaks - 10 min.
How do I make everything convert comfortably?
Ex. 5 hours allotted to person A. 5 hours to person B. 1 hour 40 allotted to tasks. And 20 min per day for breaks.
Is there a way to format the cell so that it displays hours and minutes 1:35 = 1hr35min ?? All the formats available show it as time. I need it as a quantity of time rather than time of day so that the formulas will add correctly.
Just basic enter a start time in column A a finish time in column B and column C gives you total in hours and minutes and then maybe a way to sum those hours and minutes. how to do "Math" on time and how it should be entered (formatted) for it to work properly.
How do I format a spreadsheet set up as a time card entry log so that I can enter 0543 and it appears as 05:43 AM and/or enter 1653 and it appears as 4:53 PM.
I have it formatted right now to show that data but I have to enter it as 05:43 and it appears as 5:43 AM which is what I want and the in/out times calculate with an end result of hours worked.
I want to do it without having to enter the colon.
I have the need to filter out letters put in after a number in a time card spreadsheet. I'm not sure that using a select case is the right approach. I need to allow the user to put in a number and a letter signifying what type of time it is. Each cell equals a date on a calendar. For example if the user puts in 8s then the code will add 8 hours to the total sick time, strip out the s and just leave 8 in the cell. The problem is that I need to deal with all of the other letters/symbols that they can enter. From what I know of VBA which isn't much a Select Case seems to be way to go without using a bunch of nested If statements. Here is what I would like to do but this doesn't work. This is a short example of what I have tried as far as Select Case goes.
I'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.
Here are the guidelines for the pay periods and overtime:
1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.
2. A work week is defined as Sunday to Saturday.
3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.
4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.
If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.
I'm completely willing to toss out the current method of determining overtime. This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.
Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.
Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.
I have a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.
I have a userform that time stamps on my userform as soon as i open the form, is there a way that when I submit, that the amount of time that I was on the call to be put in my column on my worksheet as minutes?
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize() If Not Range("dDate").Value = "" Then TextBox2.Value = Range("dDate").Value TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM") Else TextBox2.Value = "" TextBox2.SetFocus End If End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
I have a database which holds a field for people's credit card numbers. When I try to import them into excel it removes the last number and replaces it with a 0. So if I have credit card number 123456789123456 it will show as 1.23456E+15. Then when i format it as a number is shows as 123456789123450.
I have a scorecard created in excel 2007. All my end users are using excel 2002. Now I want to build a scorecard in Excel 2002 like what we have in excel 2007. Is it possible.
I want to display the traffic signals and 5 level indicators (arrows), based on conditional formatting.
This is out of box in excel 2007. But how do I achieve this in excel 2002? Is there a simple method?
Sub HideDatedSheets() Dim wb As Workbook Dim ws As Worksheet Dim dDate As Date
[Code]...
So what im trying to do is keep the Union* sheets shown as well as the Report* sheets shown. But i believe i have the code wrong because they are not showing up when the work book opens...
Trim(ws.Name) = "Union*" Or _ Trim(ws.Name) = "Report*" Or _
I have a userform that has a text box. If user puts a number in it and click on proceed the userform must expand and display that many comboboxes. for e.g. if user inputs 8 and then click on proceed then there should be 8 comboboxes on the form. Is it possible to do?
1. User enters a start and end time Start1, End1 (23:00, 01:00) 2. The time difference is calculated and displayed in Total1 (2:00) 3. User enters Start2, End2 (22:00, 22:50) 4. Time difference calculated - display in Total2 (00:50) 5. Grand total of Total1, Total2 is displayed in GrandTotal (2:50)
The user will input the times as 0000 but I need to convert the entry into a time format. (User enters 1235 - I need it to covert to 12:35)
If I have 3 text boxes textbox1, textbox2 and textbox3
I want ot be able to enter a time in textbox1 and then a time in textbox2 and textbox3 would give the difference. i.e. 09:00 17:00 then textbox 3 would calculate 08:00
Then all 3 times would automaically be entered in Sheet1 A1,B1 and C1
I am trying to transfer data from a worksheet to a user form, so that the end users can edit the data on the user form, save it, and the revised data is sent back to the worksheet. since the worksheet data is dynamic, i am trying to dynamically add controls in the user form. but the form displays only one data.
Set sdel = Sheets("Deliverables") Set rStartCell = sdel.Range("A65536").End(xlUp).Offset(0, 0) counter = Mid(rStartCell.Address, 4) dummy = 0 cnt = 1 'copy data from sheet to the user form With sdel 'checking if deliverables sheet has any data ' If .Range("A3") <> " " Then Exit Sub ' MyTextBox.Caption = .Range("A3").Value For r = 3 To counter If .Cells(r, 1) <> "" Then
Set MyTextBox = Controls.Add("Forms.Label.1", "lbl" & cnt, Visible) MyTextBox.Top = topadd + 30 MyTextBox.Left = 20 MyTextBox.Width = 150 MyTextBox = .Range("A" & r).Value cnt = cnt + 1 Else dummy = dummy + 1 End If Next r
I'm trying to use the COUNTIFS function & have run into trouble because the 2nd criteria is a date. Typically I would just set the range & then put "01/01/2014" if I was looking for all of the January 1, 2014 cells. However in this spreadsheet my source data contains the date with the time. (01/01/2014 11:27:39 AM). Becasue the time is included in the cell, the function returns no results when I set teh date.
I tried to use a wild card "01/01/2014*" which I thought would bypass the time but it still retuns no results. Is there a way to use a windcard with a date cell?
I have a workbook containing 2 sheets. In the first sheet I have a list of values, let's say that the list contains names:
Worksheet1 Column A Peter Paul
In another worksheet I have another column, also containing names:
Worksheet2 Column A James Harrow Paul Givens
I wish to perform a vlookup in worksheet2 but cannot use wildcards in the table array :
Vlookup ("*"&ColumnA&"*";Table1[Table or table and column];1;false). What I want should look like the following: ("*"&ColumnA&"*";"*"&Table1[Table or table and column]&"*";1;false)
In this problem, it is not appropriate to query back to front; i.e. vlookup worksheet1 instead of vlookup in worksheet 2.
I have seen an interesting post that uses macros (on this site, I think) but was not able to implement it.