My problem is that I have a worksheet tab (RawTimeSheetData) which contains a whole series of week/timecode values for a range of people.
I want to accumulate the hours for an invoice period / job code combination. As an example in the tab InvoicePeriodSummaryTimes cell D6 i want to sum all the hours from RawTimeSheetData where both cells A6 & B6 from InvoicePeriod tab = cells D6 & E6 from the rawdata tab.
How do I convert 7.30 hours into 7.21 (ie 7 hours 21min.) Note I do not wish
to use the standard hour:minute formatting.
I need also to calculate difference between dates(dd-mm-aaaa hh:mm) in workhours ( hh:mm):
The work period is 9-18 with lunch interval 13-14 The startdate and end date could be out of the work hours and i can't include the extra hours. I can have several days (workdays) at the difference, but i should maintain the format hh:mm.
Ex1:
Startdate 05-03-2013 18:34 ( date to calculation should be 05-03-2013 18:00)
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
will be equal to:
Startdate 06-03-2013 8:34 ( date to calculation sould be 06-03-2013 9:00 )
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
Ex2:
Startdate 06-03-2013 12:01
End date 06-03-2013 14:28
Time Difference 01:29
I am trying to sum up the total number of hours consumed on a particular activity within a specified period.
=SUMPRODUCT(($C38:$C176>=$B7)*($C38:$C176<=$C7)*($B38:$B176=$D5))
Unfortunately, the formula above gives me the total number of activity and NOT the total number of hours within a specified period. I attached the file I'm working on as reference.
I use a userform to make sales data entries into a database. The userform contains textboxes for:
Invoice No.Invoice DateGrossVatNet The Invoice No. is the first entry box on the userform and it is important that users enter the correct format (which is alphanumeric).
It is also important that a checking is carried out to ensure that the Invoice Number being entered has not already been used.
I use the following code to ensure that the numbers are entered in the desired format:
Code:
Private Sub txtInv_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtInv.Value = vbNullString Then Exit Sub
If (Not UCase(Me.txtInv.Value) Like "ST###") And (Not UCase(Me.txtInv.Value) Like "ST####") Then
MsgBox "Non Valid Invoice Number.”
Cancel = True
End If
End Sub
And this code is pasted lower down the VB editor window. And it seems to be working fine!
For the checking to ensure that the invoice number being typed into the textbox has not already been used, I use the following two codes combined together:
a) to check for invoice No.:
Code:
check for Invoice Number
If Trim(Me.txtInv.Value) = "" Then
Me.txtInv.SetFocus
MsgBox "Please Enter Invoice No."
Exit Sub
End If
b) And to it I add the following additional code for CHECKING that the no. does not already exist in the database (the invoice nos are located in Column A of the database):
Code:
Dim x
X= Application.Match(Me.txtInv.Value, Columns(1),0)
If Not IsError(x) Then
MsgBox Me.txtInv.Value & "Invoice Number Is Already Used"
Me.txtInv.SetFocus
Cancel = True
Exit Sub
End If
My problem with the second set of codes in (a) & (b) (for the Checking of already existing No.) is that the code allows the user to key in the Invoice No. and go through filling in the other textboxes. It is only after clicking the ‘Add’ button that a msgbox comes up to indicate that the ‘invoice no. is already used’.
I need a tweaking of the codes so that the moment an invoice no. that has already been used is typed into the textbox and the TAB button pressed, a msgbox will show up at that stage to indicate that the ‘Invoice no. is already used’. This will reduce time and avoid filling in the other textboxes and clicking the ‘Add’ button before the checking is done.
I have an Excel Invoice Template, saved as a template.
I have code that generates a new invoice number each time the template is opened in VBA Editor. The auto numbering system works great! However.... (here comes the sad part)
After I enter all the data I need on the invoice, I want to save a copy of the invoice in a different file folder, which I can no problem. The problem is, if I re-open the saved invoice, we will call it Invoice #100, excel asks me if I want to update, if I say either yes or no, the invoice number will change to Invoice #101. I'm sure you can see how this can cause a major problem if we need to compare information later on, finding the correct invoice would be almost impossible as it would not match the customers invoice number.
What I need to know is:
Is there code I can add to the existing code, to stop the increment on a saved invoice, but not on the original template?
I want add a short cut key combination to trigger to code currently in the worksheet section, at the moment it is triggered by a double clck in a row. [URL]....
View 2 Replies View RelatedI have a template invoice in excel. What I want is a macro code that when it is run the open template invoice should be sent to a specific email address !!
View 5 Replies View RelatedI have recorded a macro to filter data on sheet 'To Invoice' copy the filtered list, and paste on sheet 'Invoice' in C16.
The code just keeps looping (not looping in a code sense, it just seems to keep flickering the screen like its going over & over) until it locks up 5-10 seconds-ish and then I have to re-start Excel.
The range B2:E22 is not always populated, it could possibly be B2:E2 (one row), I dont know how to copy the exact data so I expanded the range to what I think would capture any eventuality....
I have a two documents whose names contain periods out of 70. Example is St._Paul and St._Louis_Park. When I run through the code it stops it with the error that the name cannot be found. My list looks like this..
Club Name
Eagan
Apple_Valley
St._Paul
etc.
When I remove the period it works. Is there a way to make it work with the period in there so I don't have to change a bunch of files and documents? Here is the code...
Sub OpenUp()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks(ThisWorkbook.Name)
Set ws = wb.Worksheets(1)
With ws
TotalRow = .Cells(1, 255).End(xlDown).Row
Finalrow = .Cells(TotalRow, 1).End(xlUp).Row
End With
For i = 2 To Finalrow
clb = ws.Cells(i, 2)
psw = ws.Cells(i, 3)
psw2 = ws.Cells(i, 4)
mnth = ws.Cells(i, 5).........................
I really need a validation code for Cell B15. I realize that a macro could do this, but a validation code is what I really would need:
Cell B15 can only allow at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits):
I
I I
I I I
IV
IA
I IA
I I IA
IVA
or (some combination examples):
IA & I I I
I I & I I IA
I VA & I IA
If the user fails to meet these requirements, then he should get an error message telling him to try again.
Im looking to create an invoice-type format from data in a row but only when the client requests it.
So, if there is a Y in column R. i.e. the client wants an invoice format then.
I have been given a template format by the team manager that I have to use. Its far from ideal, well for me anyway, for moving things around and Im struggling to get anywhere with it.
Im trying to take the data from the row in the source / client worksheet, which will be created via a code from here, and transpose it to a copy of the template worksheet which sits within the same workbook. This is made more complicated because in some cases the data for the invoice will be in two or more rows, because there will be a few items on the same invoice, but they will still be on the same worksheet.
The source / client worksheet has data from columns A to T with a header in row 1.
The details will need to be transposed from the relevant row from each column to the template worksheet as follows
From column in source / client worksheet To cell in copy template worksheet.
From - To
CB2
AB3
BB5
HB7
IB9
GB11
EB13
FB15
RB19
SB21
QB23
OB25
PB27
This is where it gets messy, or more messy should I say
MB33
JB35
LB37
KB39
TB41
NB45
For each of the six sections M to N above there may be more than one relevant items which will be on rows 3 onwards of the source /client worksheet.
So, ideally Im guessing based on if there is data in column A of the source / client worksheet then M to N above will need to copied downwards, i.e. below itself on the template worksheet say from column A and B rows 33-45 copied to A and B rows 47-59 until all the data is copied over.
Blinking eck ... this is a nightmare a real nightmare. This more than one section above is really bad Im at a complete loss.
Although if its really not possible they may have to have another template worksheet with the second, third etc items on as I just cant see this working.
I have a cell (B10) that has an IP address in it. I want cell B11 to automatically reflect the same first 3 octets of B10, but the last octet accumulated by +1.
Example: 172.23.0.128 would be in B10. I would like to have a formula in B11 to increment B10 by +1 to show 172.23.0.129.
I am trying to set up a budget spreadsheet for a friend. The sheet will have the usual incoming and outgoings with a surplus at the end of each month. This surplus needs to be accumulated each month in a particular Account (Cell).
Once the amount in this cell reaches £5000 any surplus accumulated each month from that point needs to be deposited in another account (Cell).
I will try explain what I want to calculate but cannot find the way. I want to calculate a result from two digits, then remember the result and add to this sum next digit typing in the same cell. Example:
colum A names
colum B numbers
In cell B1 I will write 5, after enter result in B1 is 5.
In cell B1 again write 7 result in B1 is 12.
In cell B1 again write 6 result in B1 is 18....and so on.
In a cell I have a description, say "FOOD" then another cell with amount say 2, then in another cell I want it to be looking and if it sees "FOOD" entered, it takes the amount 2 and adds it to a running balance.
Cell A1 = Food
Cell B1 = 2
Cell H1 = (looks and if Food) adds 2 to cell
I need a formula or script that accumulates data that a person enters into a particular cell.
[INPUT] a6=enter service requests closed today
[OUTPUT] b6=Service requests closed last month
[OUTPUT] c6=Service requests closed this month
I want to make this as simple as possible and the person enters her data in the same cell every day (a6). The script or formula should (in the background) then take todays total and and add it to the accumulated total and show the outputs in b6 and c6.
If a person enters data multiple times on any given day, the script or formula should recognize only the last entered number for that day. This will control user errors in entering.
I thought about just entering the raw data in a separate spreadseet and using 1 cell for each day, but then a person would have to enter totals on one sheet and look at the results on another seet. not good
I also thought about taking the above solution and combining them on one sheet. this would work, I guess, but I really need a sleek lean sheet that they add to the same cell each day.Also there is additional data that I still need to show on the screen and I really hope I do not need to move this data.
Data Table including-
List of Identifying Code Numbers for customer invoices
Multiple repetitions of individual Identifying Code Numbers in list
Various data in table range including Various Values of invoices from different dates for each repetion of Identifying Code Number.
- Wish to find largest invoice for each Individual Identifying Code Number in the table without using a pivot table.
i have tried combining Max and Large functions with Vlookups etc.
As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.
Nights, holidays and holiday night have different price/hour.
The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for me…
On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!
Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)
I have letter and number combination code in two collumns and they differ for 10.000 numbers:
BAM98314
BAM88314
BAM90000
BAM80000
As you can see the left code is for 10000 numbers higher. the letters are allways the same. In the event that this isn't so, if difference between codes in same row is more or less than 10000 numbers. I was thinking on making conditional formating so the cells with wrong difference would be marked red, but I do not know how to make formula for this difference.
I have months ( 1 to 12 ). Every month, a set of tasks need to be executed which takes "x" number of hours.
So if I have 1 machine working 3 hour in the month 1, the total time spent is 3 hours. Fairly simple right !
Say on month 2, a set of tasks take 4 hours. total time that my first machine takes is 4 hours. But i want to introduce 3 new machines in this month, which will execute the first month's tasks. So total time spent here is ( 3*3 + 4*1) = 13 On month 3, task time is 6 hours. And I am introducing 2 new machines.
So total time is 2*3 ( time taken for 2 new machines to perform first month's tasks ) + 3 * 4 ( 3 new machines introduced last month will now execute second months task) + 6 * 1 ( time the first machine will spend on month 3 s tasks ) = 24
How can I total overtime based on a "Reason Code" and whether the employee chose "Cash" or "Time" compensation?
View 2 Replies View RelatedI need a VBA macro code to get the output like in the excel file which I have attached with this thread. Which means, I need to group the data for every two hours. in the output I need all 4 cols namely A, B, C, D along with extra col namely, interval based on which I need these groupings to be done..
The code has to be really flexible so that it works for all dates and times in the files. Because like this I have to do for 2000 files.....
Original link: [URL] .....
plant area.xlsx
I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.
In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time.
Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.
Lunch is not paid. Holiday and vacation hours get calculated at the regular pay rate. Overtime is anything in excess of 8 hours per day and/or in excess of 40 hours per week and/or over 5 working days per week. Saturdays for most the employees will be overtime because it will be their 6th workday of the week; but it will be regular time for one employee as it will only be his 5th workday of the week.
For accounting and payroll purposes, we need the totals to display in both hour and decimal format.
So far, I have Lunch, Regular and Overtime hours figured out, but I still need to work with Saturday, Vacation and Holiday hours. Also, currently, the time in and out has to be typed in with the colon and AM or PM. Is there another way to input the info without having to type in those items? I'm trying to make it as user friendly as possible.
The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie :
Difference between
02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes
=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))
The following displays it in the Hrs and Mins format
=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))
I'm attempting to make a simple time sheet for a handful of employees. I'd like to enter the clock in time and clock out time for each day. The end cell should be the running total for the week. The tricky part for me is having the formula subtract an hour for each day that is over 5 hours.
View 3 Replies View RelatedFormat Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....
View 9 Replies View RelatedI'm having difficulty to calculate hours between 2 or 3 days exclude non working hours.
Attached is the example of start date with time & end date with time.
The situation is like "when the case log in till the case assist in working hours." so i will get the hours from case log to case assist.
Testing.xlsx
