The business hours considered here is 8AM - 5PM, Start time in R9 and End time in T9. Now the problem is its calculating the correct value when the days are same, for e.g.,
Condition 1
When I am giving "31 March 2009 15:00:00" as start time (R9) and "31 March 2009 23:00:00" in end time (T9), I am getting the correct value. i.e, "2:00:00"
Condition 2
While giving "31 March 2009 16:00:00" as start time and "01 April 2009 09:00:00" as end time I am getting a value of "1:00:00", actually the value should be "3:00:00".
I need to do an hour calculation on two cells which have dates and times in both. the first cell is a call that we get from a customer and the second is the date and time in which that call is closed by us...meaning that call is complete.
I need to calculate how much time in hours did it take us to complete that call for the customer. I need this calculation to respect our business hours of Monday to Friday 8am-5pm and closed on Saturdays and Sundays.
here are some examples.
from - 2/12/2004 13:00 (thursday) to - 2/13/2004 9:00 (friday)
answer should be 5 hours
from - 2/13/2004 14:00 (friday) to - 2/16/2004 10:00 (monday)
What calculation would I enter in a results cell if I wanted to find the delta between 2 times in date format that repersent just the business hours of 8am-5pm, therefore excluding after hours and weekends.
eg.
Date 1 Date 2 Result Time 23/07/07 8:00 24/07/07 14:55 15:55
So far I can't get the caluclation that will compensate for the after hours and week ends.
I am currently doing some work which is taking ages and i wonder if there might be a quicker way of completing the work.
Each Row contains a User ID alone with the volume of business submitted under that user ID via a specific payment route, the problem i have is that i may have 10 of the same user ID's but each with a different business volume and payment route attached, I have already sub totaled by User ID so tat can see total business volume for each user however, I want to delete all the duplicate user IDs leaving only the row with the max volume of business attached.
I have approx 40,000 user ID's so i need the solution look for sets of User IDs and leave only the one with the biggest volume of business attached to it.
I am extending rows on a salary schedule. As you can see below, the rows include: Bargaining Unit (GP) Schedule(2A) Range(5) Step (A) Hourly (S) and the hourly wage(11.52)
The Steps currently go from A thru M but I need to add N thru Z and after step M there is a 3.75% increase for each step.
You can see that this is a csv file (comma delimited). All of the other info needs to autofill and only the new steps (N-Z) will change and the wage for each of the new steps. The last row is 2 decimal points.
I want to use a calculation within the Rows collection, but I get a type mismatch error. I realize it boils down to syntax, but I don't know what I don't know.
1. In place of, say, Rows("2:37").Select...I want to use Rows("2:x-2").Select where x-2 is a simple subtraction calculation. I also want to use two variables. I'd hope to be able to infer what to do from above, but just in case:
I want to copy all rows from my input sheet that have a date less than the date in cell B3 of the output spread sheet and are contained in the stock table (range A7:B17) Sedol in column B Is a unique identifier. I started to do this but am lost.
How would I write a function to give me the last ten business dates moving horizontally from today's date with yesterdays date the being the last date? So if A1 is 2/26/14, how would I populate B1-B10 with wanting B10 to be 2/25/14 and moving backwards but only with business dates?
I have a spreadsheet (attached) containing 3 columns, week commencing (c), week ending (d) and business week (e). The question is can a user enter a date in one cell (b4) and have the next cell (b5) return the corresponding business week, I have come up with a couple of solutions involving hidden columns but was wondering if there is a way to do all of this in one cell.
My file name constantly changes, it will always be (minus 2 business dates from today)
Now when my macros run it goes between different worksheets so I need to tell it to go to right workbook. I tried below but getting an error 438 'Object doesnt support this property or method'
I am looking to calculate business days - more specifically Monday through Friday. I am not currently worried about holidays or vacations, yet I wouldn't mind including it if I could have a list to "check" from.
I tried previous searches, but found a few functions that I don't have on my computer.
I'm using this formula to pick all rows from a huge range of cells "DATA" in another sheet, by matching the first column value (SAPDATA is the range that contains all the first column cells of DATA).
All possible first column values have been stored as a dropdown in cell D4, and whenever I change the choice in the dropdown cell, the retrieval of those hundreds of rows takes ages in excel.
I have a spreadsheet that tracks all field equipment inspections. This year, certain equipments will need to be inspected twice a year. Ideally, if it was inspected in Quarter 1, it needs to be inspected again in Quarter 3 and if it's Q2 then it'll be inspected again in Q4.
I need a formula that will tell me when the next inspection is due.
This is what I have so far: Column I (Formula): date of last inspection Column J (manual entry): "yes" for equipment that will be inspected semi-annually Column K (formula): The Quarter is was inspected based on Column I's date Column LThe quarter the next inspection is due
When i open a new thread, so here i am doing so at the advice of him. The message below is specific to him to reply to but for anyone else that might want to help me with this set of formulas to resolve my worksheet headache and lack of skills in excel. Please feel free to have a look at the worksheet i posted and the comments in each color coded cell.
I am attempting to create a formula that will select a value from a lookup table where that value is related to a specific date BUT that date, derived from an input date (any day within a selected month) must always be the first BUSINESS day of that month.
For example:
I have a workbook with two worksheets in it.
The first worksheet is the input page.
On that page I want to have a list of dates which display the FIRST BUSINESS DAY of a month - e.g.
Cell A1 - Date entered as 19/9/12 and displayed as 19-Sep-12 Cell B1 - NEEDS THE REQUIRED FORMUAL TO CALCULATE THE FIRST BUSINESS DAY OF THIS MONTH (SEP) (in this example, this should be 3) and displayed as 03-Sep-12 Cell C1 - Will display the value from the lookup table on worksheet 2 (see below) related to the date in B1
The second worksheet is a lookup table containing a sequential list of dates from 28-Jan-69 through to present (19-Sep-12) in Column A.
The adjacent column B contains the related value to that date (the value being a foreign exchange rate) - e.g.
Daily Date USD
29 June 2012 1.0191
1 August 2012 1.0507
[Code] ........
Worksheet 1, Cell C1 should display the value of 1.0266
I am trying to set up a spreadsheet to record business costs, I have a column [A] and [B] with start times and finish times respectively. [C] is the shift length say 10 hours but I always get an error if its a night shift as the start time is invariably later than the finish time. Is there a way around this without inputting the dates into the cells as I am trying to not duplicate data entry.
computing the number of business hours between 2 dates.
Given the following information: - Business Hours: 8am -5pm (8:00-17:00) - Business Days: Mon-Fri - if entry queued falls on a weekend or beyond the business hours, it should count the first hour on the next business hour.
Example: scenario 1 = Starts Sunday, 10am and Ends Monday 9am; count of hours should be 1 scenario 2 = starts on Monday 6pm and ends on Tuesday 10am; count of hours should be 2
we have salespeople in all 50 states and each state uses different rules for business days. Some states include Saturday as a business day, some don't, and Alaska uses 5 business days including Saturdays. We are open 7 days a week.
What I am trying to create is a worksheet that has each day of the month going down and in the cells next to it, a column for states that include Saturdays as business days; so a date 3 business days out would appear in the next cell. In the cell after, a date 3 business days out not including Saturdays. And in the last cell for Alaska, a date 5 business days out including Saturday. Of course federal holidays are all excluded.
Something like this:
March w/ Sat w/o Sat Alaska 1 5 5 7 2 5 5 7 3 6 6 8 4 7 7 10 5 8 10 11
Now Vermont has extra holidays certain months so I would have to adjust that in the formula for the middle column during those months.
I have created a useform in VB which will determine business days. I need help in the coding part.
In my user form I have text box, I have a calender and I have two command buttons, 1 Calculate 2 exit. What i would like to achieve is when i type a number in the txtbox and press cmd button calculate the code will shade the the date in the calender so for example if I type in 10, then the code should shade 13/02/09 on the calender since 13/02/09 is the 10th business day.
