Assuming that a workday is from 8:30am to 5:00pm. (also need to not calcualte Saturday and Sunday but this example doesn't show it).
Wednesday, 7/18/07 at 4:00pm (minus) Friday, 7/20/07 at 9:00am (should equal) 10 hours or 1 day, 1 hour and 30 minutes.
Basically what I want to do it determine how much work time has elapsed from the time a message was left on voicemail to the time that someone picks that message up. We only want to assume that a message can be retrieved duruing business hours but a message can be left at anytime!
I have a data of complaints where I need to present it to the Management in such a way that the SLA period of 8 hours does not pass. Our office working hours are 7 AM till 7 PM. The complaint received should be escalated to concern section within 8 Hours of SLA time. I have the list of dates with received time. The complaint which could not be escalated today would be escalated next day. In this case is should deduct 12 Hours (7 PM to 7 AM, Non-working hours) from the time. How can I insert escalation date so as that it would deduct non working hours from it.
I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!
For example Start time: 21:00 Finish time: 06:30 Hours worked: 9.50
I want to set up a spreadsheet that can show my work is completed within a service level agreement, based on working hours of 9-5 Monday-Saturday.
I want to be able, for example, to log that a piece of work is reported at 09:00 on a Monday and completed at 10:00 on Monday and for excel to calculate that as 1 hour until completion. Fine so far. But what if that job was closed at 10:00 two days later? Based on an 8 hour working day, that should be 17 hours.
And what if a bit of work comes in at 17:00 on the Saturday. No one works the Sunday. Say the job is completed at 10:00 on the Monday. that should calculate as 1 hour to completion.
I am trying to add/subtract dates and times to return one sum; but I only want to count the work week (5 days M-F) and work day (8 hrs.) 8am- 4pm. This is to compute the actual turn around time for a certain process.
i.e.: Date ReceivedTime ReceivedDate ApprovedTime Approved10/1/20081:00 PM10/15/20089:00 AM
The total hours this process should equate to is: 9days and 6hours.
I have a cells with a date and time in each cell. I want to subtract the number of days between the cells only. Is there a way to do that with the time in the cell? If not, how do I remove the time in each cell?
When I am converting a time from Hours/Minutes to Hours/Tenths, Excel is not converting it consitantely. EXAMPLE: 1:15 = 1.25. When I format the cell to present only one place past the decimal point, sometimes the cell will round up to 1.3, and other times it will round down to 1.2. What am I missing?
I have a problem concatenating time in excel if it is of Date + Time format.. What formula do I use to just add the time by, say, 3 hours? I'll need to use, say cell B2, which will add 3 hours to it.. What formula do I use?
Column 1: Date Call is received by helpdesk Column 2: Time Call is received by helpdesk Column 3: Drop-down list indicating Priority of call (High,Medium,Low)
In Column 4 I want to enter a formula that adds working hours only to the date and time entered in Columns 1 & 2. The time added will vary depending on what is entered in Column 3 (e.g.: if priority is High add 1 hour, Medium add 2 hours, Low add 3 hours). The working hours i need to adhere to are 09:00-17:00, Monday-Friday.
i work dispatch at a company and we're trying to track how long it takes for a technician to arrive on-site. my worksheet currently includes the date of call-in (E1) , time of call-in (F1), Service Date (G1), Service Time (H1), among many other field. the easy solution is to use the formula (G1+H1)-(E1+F1), and use the answer as the total amount of time, but the problem is if a customer call in at 3:00 PM and we service them at 9:00 AM the following day, it looks like it took 18 hours to arrive on-site, when in reality, since we close at 5:00 PM and open at 9:00 AM, we only took 2 hours to arrive on-site. is there any formula i can write to account for non-business hours and weekends, or is this a bit above what i should expect from excel?
I need to calculate the difference between two cells that are formated like this: 11/10/2006 13:00. Simple subtract only works when the date hasn't changed (the subtract formula seems to focus on the time and ignore the date).
I found a formula that will work, but the results are in regular numbers, I need the hh:mm format and if I change the format of the result column, the time displayed is wrong.
The formula is=((B2-A2)*1440)/60)
If my result is 122.6 in regular numbers, when I convert to hh:mm I get 14:00.
Is there a better way to do this? Also, is there a way to eliminate weekend & holiday hours from the calculation? This is not as important as getting the results to calculate correctly in the hh:mm format.
I have a column A with some dates at this format "dd-mm-yyyy hh-mm". In this list, some hours are missing, and I don't know which ones.
In order to find the missing hours, I created an additional column B, displaying all the hours, this way: B1=01-01-2006 00:00:00 o B1=A1
And then : B2=B1+"01:00:00"
Then, in column C, I compare both columns A and B: C1=IF(Value(B1)=Value(B2),ok,missing)
As a result, I first get some "ok", and then more and more "missing" statements, even though the dates look the same.
I checked the numerical values of my dates. At the first "missing" statement, I saw that there is a slight difference in the numerical values, at small decimals...
Can you tell me how to define my date /time in a good way, in order to avoid this kind of problems, please? (I could take the int part of the numbers, but this could lead to further errors, so I prefer to define my dates in the right way, from the beginning).
I am trying to find a way to calculate the number of hours between date/times found in separate rows. The attached data set will help to envision what I am talking about.
For each couple of rows, I need to find a way to calculate the number of hours elapsed from row 1 to row 2. In the first example, to calculate the number of hours between 12/2/2009 8:56:51 and 12/4/2009 6:35:27.
I have a spreadsheet that contains a list of names/numbers/dates and I have to manually go through the list each day highlighting the row if it contains a date that is more than 48 hours old. How to accomplish this in a macro? Example:
That is the layout of the spreadsheet so I would have to manually highlight rows 2 and 4 because the date in Column D is greater than 48 hours ago. The range is A1:D25 and the cells are changed/updated daily.
I calculate total labor hours entered into a CRM system and use it for various reports, but it's incredibly inaccurate for the following reasons:
Any labor put in between 6pm EST and 8am EST from Mon-Fri is considered "on call"All labor from Friday 6:01pm - Monday 7:59am is considered "on call"Labor entered on a company recognized holiday is considered "on call"Not every employee takes call
So, what I'm trying to do is take the total labor and pull out only the hours entered outside of the criteria above. If it were simply based on time during the week, i'd be fine, but I have no clue how to do everything else.
In the end, my new labor hours would be split into "Business Hrs. Labor" and "On Call" labor.