I have a list of times in a column. I'm looking for a macro that will go down the column and record all the times that lay between 5 and 7am, 1 and 3pm (13:00-15:00) and 8 and 10pm (20:00-22:00)
How to write it?
Looking for a formula for total cost based on a list of hours for each employee and a table of rates for each employee.
Please see attached file : Formula.xlsx
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 ",""))
A1 is 10 (10 hrs worked) , A2 is 10:30am (in time), A3 is 9:00pm (out time), A4 needs to be the total hours and minutes between A2 and A3 based on the hours listed in A-1. What i need is a formula that will calculate the hours and minutes between the 2 times based on hours entered in A1 but that will also compensate for a manadatory 30 minute lunch that needs to be deducted from the total hours if hrs listed in A1 are more than 6.
example: worked 10HRS, 10:30am to 9:00pm, Total hrs is 10hrs 30min, which should be just 10 since the lunch is a none work time and must be subtracted.
If a person worked more than 6hrs, they must take a lunch. if they worked less, than 6 then they don't have to. I need a calcuation to recognize the greater than, less than factor into the equasion also.
I have created a table to record the daily hours worked by staff, however ,since the data I'm dealing with is quite large (over 300,000 rows), I need a function/formula to make populating the table quicker and less daunting.
View 2 Replies View RelatedI have the day of the week in B1 Emp Nbr in B2 Hours worked on B11
What I need a macro to do is match B1(day of the week) with the corresponding offset column in F1-R1, Pos and Post the Emp NBR(b2) and Hours(b12) in the corresponding column (F4 and G4)
I then will key in a new Emp and the hours and click the macro to execute the move over to the correct column and down one row.
Do this until I post Emp 999 at which time it will exit the macroOzgrid weekly hours.xlsx
The next day I will change the day in B1 to 2, corresponding to TUE, and it will move over to the correct column (H4 and I4) and post the daily data until I key in EMP 999
Then continue for the rest of the week.
I've seen a lot of traffic around running macro's on a clock at regular intervals. But what if I want to run a Macro (Macro1) at 8:55, 9:55, 11:55, 12:55, 13:55, 16:55 and another Macro (Macro2) at the top of each of those hours? The string of times is not consecutive, so I have built a table to define the release times. At each of these times I need to run a macros, and I'm not sure where to begin.
View 3 Replies View RelatedNeed VBA macro to read and sum data from multiple worksheets
The “summary” worksheet contains list of all employees. I need to update the total number of hours for each employee by scanning all worksheets in the workbook as follows:
Once I click on a "update" button, I need to scan all worksheets (I have one worksheet for each week) which contains the total number of hours for employees by week. I need to calculate and add the total number of working hours for each employee then move to the next worksheet and so on ..
Once all worksheets are all scanned, the final total of working hours get posted in front of the employee name in the assigned field.. I need to do this for all employees. I also need a macro to create a new worksheet.. I attached an example with little description inside .. Labor Detail Job to Date-2.xlsx
This is for employee time sheet and schedule. I have multiple employees work a specific shift on various days. I need the calculate the hours worked each day and then have them totaled at the end of the period. Here is and example:
Employee Monday Tuesday Wednesday Thursday Friday Saturday Sunday Total Hours Total Cost
John Smith 10-5 12-6 9-2 5-10:30 3-8:30
How do I get it to calculate the difference in the same cell, convert it to hours, add up all the results and place it in the totals columns then calculate those totals by their pay rate to get my Labor cost?
I am working on a spreadsheet where I want to count the number of calls to our service desk between specific hours (ie. 6:00 a.m. - 7:00 a.m.) up through 8:00 p.m. I have looked at all the forums and tried all the formulas that seemed to be what I was looking for and it isn't working! I am adding the information to a summary spreadsheet from which I am gathering the information from other sheets in the workbook. This will be an ongoing spreadsheet done weekly for every month.
Examples: I need to know how many calls from these times on a summary sheet.
6:23
6:28
6:59
7:09
7:35
7:38
8:00
8:26
8:34
8:42
8:43
9:02
9:04
9:23
9:27
9:31
10:06
10:23
10:37
10:51
11:05
11:09
11:19
I receive data from a company I deal with that has the date and time of all of my transactions with them listed in an excel worksheet. Only problem is that the date/time that I receive is not in my timezone, which is a bit of a problem for me!
What I want to do is to be able to adjust the date and time shown in the cell forward by 14 hours, so that the data is shown in the timezone that I am currently in. So the end result for the first line of data in the attached file would end up being 2006-08-11,10:28:51 instead of 2006-08-10,20:28:51.
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
Basicly we pay single rate between 8.00am and 8.00pm at all other times and on bank holidays and weekdays we pay an enhanced rate. If I can calculate the standard hours then the remainder of the hours are enhanced. Aim to count how many work hours fall between a standard start time (SHS) and a standard end time (SHE) (currently 8.00am and 8.00pm), that do not fall on a weekend and are not a bank holiday.
Column B = Start time (eg 06:00)
Column C - End time (eg 22:00)
Column D= date (from which day of week is derived)
Column E = Y or N for whether it is a bank holiday or not..............
I would like to get formula which can show how many hours are after midnight from two times, example:
PHP Code:
clock in > clock out = hours after midnight
23:00 -> 1:00 = 1:00
01:15 -> 4:15 = 3:00
Please see this example: [URL] ......
I need the difference between two dates/times field in hours or minutes.
Eg:
A1 B1
1/4/05 10:00 2/4/05 14:30
The result should be 13.5 hours, considering only 8 hrs per day, only business days and 8 to 5 workday.
I cant figure out why this formula is not working. I am trying to alter it a little, but I still think it should work. Here is what I am trying to do. I am looking to calculate the time difference between two work projects, but exclude time when the office is closed. So someone starts a project at 2pm and finish 10am the next day it will show a result of 4 hours because the office closes at 5.
Here is the formula. It is the time formula from cpearson.com ...
Is there a way to calculate the number of hours between two times specified like this:
Start
End
Hours
Comments
Mon 6:00p
Mon 9:00p
3.00
Same day
[Code]..
I think I can write a UDF and that may be the easiest. Is there a straightforward way without a UDF?
I have problem to calculate the time which is more than 23:00:00hrs in a file. I need to compare the benchmark time and the difference of time(start time and end time) and show pass/fail depending on benchmark set time. For few of the times which is more than 23:00:00 hrs the format is taking different(example: for 49:15:48, it is showing 02/01/1900 01:15:48 in formaula bar) for which i am not able to use formula and know pass/fail.
View 4 Replies View Relatedi need to get a formula that will calucate hours and min. its for how many hours the employee has not worked. some of them would be strait hours some would be just min there is no way to tell.
example
lates 2 hours
anp(absent no pay) 12 hours
sicks 55.5 hours
no calls
early outs 21 min
(this is just an example if it were real this person would be fired)
i know this adds up to 69.85 hours but i can't fuiger out a way to get it to calucate in excel. i know i could have it all changed to min and then devied by 60 to get the hours but how do i get it to read what is mins and whats hours?
I'm trying to do a simple calculation of the # of hours between 2 times, rounded up to the nearest hours. Somehow along the way I've ended up with this massive formula below, which is doing what it's supposed to do except for certain cases (like the one in the attached file...where it says the difference between 2am and 3am is 2 hours) =ROUNDUP(IF(B2="",0,IF(A2="",0,(IF(B2<A2,((((B2+1)-A2)*1440)/60),(((B2-A2)*1440)/60))))),0)
View 4 Replies View Relatedin speeding up the below code as it does not seem to matter whether I use For, If loop etc - it runs too slowly. Basically we have one sheet which is input in a different timezone that needs converting to GMT... since daylight saving I wrote some code to cover the +/- 1 hour difference (this seems to work quite well) yet when it comes to converting the actual cells it runs incredibly slowly. I appreciate any help but its only the code after "Sheets("data").Unprotect" that i think needs changing. The time stamp is in column 7 - I hope this makes enough sense - which then gets converted by either 4.5 or 5.5 hours dependent on british summer time.
Sub TimeConvert()
With Application
.Calculation = xlCalculationAutomatic
. ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
Dim FirstDate As Date
Dim Position, DayIndex, lgrow, lstrow As Long, TargetMonth As Long, Stweekday, Enweekday, Tdate As Date, TargetYear As Long, cell As Range
Dim bst As Boolean
TargetYear = Year(Now)
TargetMonth = 3
Position = "L"
DayIndex = 1
I have developed a functional User Form that works like a charm. The form will Add New Records to my database and Get Next and Get Previous records. This all works like a charm. I am stuck on how to search by Last Name or Employee Number and then to Update the Existing Record. If I change anything in an existing record as the VBA is written today, my only choice is to add a new record to the end of the database. I have added Command Buttons, but I'm lost as to how to write the code. Here's what I have so far:
VB:
Dim currentrow As Long
Private Sub cmdGetNext_Click()
Range("A2").Select
ActiveCell.End(xlDown).Select
lastrow = ActiveCell.Row
[Code]...
im trying to do is create a database of autocad drawings based on style of house number of bedrooms sq ft and if theres a garage. what i am wondering is how can i add a search function. for example i want to find a Cape with 2 bedrooms...i have all the information on the spreadsheet. what do i need to do to be able to search among the spread sheet without using the find function this is just the beginning of the data
I need to calculate the difference between a start time and end time in hours and minutes.
Start
01/07/2008 11:40
End
01/08/2008 19:28
Start and End columns are formatted as 'Custom' m/d/yyyy h:mm.
I'm not sure what formula to write to calculate the hours and minutes between the two times. Everything I've tried doesn't count over 24 hours. Also what do I format the result cell as?
Im trying to calculate hours and minutes between two dates/times over more than 24hrs. I have:-
A1 is 18/10/08 13:14, B1 is 20/10/2008 12:20, C1 is 1:23:05
Using the formula C1 =INT(B1-A1)&TEXT(B1-A1,":hh:mm")
But what I really need is just Hours and Minutes so the above should read 47:05.
I am trying to figure out a difference in hours between date & times.
Cell & Info:
A1 = 07/12/2008
A2 = 02:00:00
A3 = 07/12/2008
A4 = 04:00:00
So in cell A5 i want the difference which is: "02:00:00"
but i dont know how to include the data in the formula....
etc if:
A1 = 07/12/2008
A2 = 01:00:00
A3 = 08/12/2008
A4 = 01:00:00
A5 should be = "24:00:00"..
In a single employee Timesheet spreadsheet:
....................Mon.....Tue.....etc.
In:................6:65.....6:71
Out:............12:07....12:05
In:..............12:62....12:41
Out:............15:66....15:73
Hrs Worked:.....8.46......
How to format the cells in the daily columns and what formula to calculate 'Hrs. Worked'?
I need to calculate billable hours.I have a start date/time and end date/time.Then I need to subtract out all time between 05:00:00.000 and 20:00:00.000. These are not billable hours.But I can include all weekend time.And I can include all holiday time.I need to accomplish this with formulas, no macros.
View 2 Replies View RelatedI got a problem with a time calculation, if it's even possible.
I want to calculate the amount of hours that have been woork in a slot.
Example:
First slot is from 08:00am till 17:00pm, time cost 100%
Second slot is from 1700am till 24:00, time cost 110%
Third slot is from 24:00 till 08:00 am, time cost 120%
I'm working from 08:00am till next day 01:00am
So my hours are:
9 in slot 1,
7 in slot 2,
1 in slot 3.
Is this possible to calculate in Excel?
I got a file attached with a sort of layout.
how to roundup and total time using formula? specifically with the format mm:ss:0? i have 3 columns with these formats and would like to add them up but can't seem to get the right formula.
View 4 Replies View Related