Separate Hours Based On Time / Date / Holidays
Jan 9, 2013
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.
View 1 Replies
ADVERTISEMENT
Feb 12, 2010
I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this.
I have a total amount of time that a machine should take to finish a task. I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task excluding weekends, and holidays (End Date). This would be based on a certain number of "working hours" (hours in the workday minus break periods) that would be calculated in another cell.
View 9 Replies
View Related
Apr 4, 2013
find a formula that will calculate the hours between the two below values but only take in to consideration the business hours (from 9 to 17) and exclude any weekends?
08/03/2013 13:32:00
02/04/2013 09:32:50
View 4 Replies
View Related
Feb 13, 2010
I would like a solution for the automatic calculation of the end date and end time for project tasks. I have already spent hours on the issue, thanks for any help on this.
The parameters are:
A1 = Start time 08:00
B1 = End Time 17:00
A2 = break lunch 12:00
B2 = back from lunch 13:00
Task parameters
A5 = start date 01/03/10 (entered manually)
B5 = start time 10:00 (entered manually)
C5 = duration 02:00 (hrs entered manually)
D5 = "end date" >>> (to be calculated exluding breaks and holidays)
E5 = "end time" >>> (to be calculated exluding breaks and holidays)
the next line should be filled in automaitically according to the hours needed and the previous end date & time
A6 = "start date" >>> (after line 5: to be calculated exluding breaks and holidays)
B6 = "start time" (after line 5: to be calculated exluding breaks and holidays)
C6 = duration 14:00 (entered manually)
D6 = "end date" >>> (to be calculated exluding breaks and holidays)
E6 = "end time" >>> (to be calculated exluding breaks and holidays)
and so on for every new line down.
View 9 Replies
View Related
Apr 9, 2014
I'm using excel 2010 on a pc. I need to create a calender. So far I have been using the tutorial for the pop up calender. The result I'm looking for is to set up a specific time frame and have the calender provide the "due" date. I would like the due date to exclude weekends and holidays. Would I be able to do this with the pop up calender?
View 3 Replies
View Related
Jun 27, 2014
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,)
View 1 Replies
View Related
Nov 21, 2007
I want to create a formula that will do the following Each worker is entitled to 21 days holiday per year this will run from 8 Jan 08 to 7 Jan 09. But if a worker starts say 15 Apr 08 he would be entitled to less than 21 days. I would just like to be able to put his start date in a cell and then automatically generate how many days holiday he would be entitled to from 15 Apr to 7 Jan.
View 2 Replies
View Related
Jun 17, 2014
I have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
View 1 Replies
View Related
May 30, 2012
I want to add hours to a date-time cell to get result in date-time.
Format of cell A1 is d/m/yy h:mm AM/PM
Format of cell A2 is General
Format of cell A3 is d/m/yy h:mm AM/PM
I want to add A2 (number of hours) to A1 to give A3.
The formula I used is A3=A1+Time(A2,0,0)
The formula works perfectly fine when A2 is less than 24, but when A2 is more than 24, the date doesn't get changed.
View 6 Replies
View Related
Jan 29, 2014
I want to calculate the time in hours between to dates excluding non-working hours and sundays and holidays.
My office timings: 10:00am to 05:30pm Saturday is working day. Only sundays and holidays to be excluded.
View 8 Replies
View Related
Aug 6, 2013
I have got a formula that can separate day shift hours from night hours, in this case night begins at 7pm to 7am, however the problem is after 12am we get into negative numbers, what formula would fix this and can be combined with the formulas below?
E10 = 19:00 or start of night time hours
B3 = start time
C3 = end time
D3 = day hours workeds =24*IF(E10
View 1 Replies
View Related
Oct 10, 2009
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?
View 3 Replies
View Related
Aug 23, 2008
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?
View 3 Replies
View Related
Feb 22, 2008
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.
View 6 Replies
View Related
Mar 19, 2007
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?
View 12 Replies
View Related
Nov 22, 2006
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.
View 9 Replies
View Related
Aug 7, 2007
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).
View 3 Replies
View Related
Jun 20, 2008
I am trying to subtract 8 hours from a date/time. What I need to do is to capture that lead time using only weekdays, and working hours of 8am-5pm.
For example:
Date/Time
6/23/2008 9:18AM (Monday)
Subtracting 8 hours from this scenario would give me
6/20/2008 10:18AM (friday)
View 3 Replies
View Related
Jan 21, 2010
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.
View 2 Replies
View Related
May 2, 2014
I just want a macro to split the hours to a date and time in column A. I just preferred macro rather than a formula.
Ex: [Code] .......
Column B1 should be: [Code] ........
I've also attached the file : hours split.xlsm
View 5 Replies
View Related
Dec 22, 2009
I want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.
Note that the night shift carries over to the next day.
Start/Finish
21/12/09 07:00 to21/12/09 11:09
21/12/09 07:46 to21/12/09 14:41
21/12/09 12:13 to21/12/09 22:08
21/12/09 16:40 to21/12/09 18:05
21/12/09 19:40 to22/12/09 02:34
21/12/09 23:20 to22/12/09 04:39
22/12/09 02:06 to22/12/09 06:15
View 9 Replies
View Related
Sep 11, 2011
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.
View 5 Replies
View Related
Feb 15, 2012
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:
Name.......Phone Number:......Ticket Number......Date Added
xxxx........555-555-5555...........5555555............2/12/12
xxxx........444-444-4444...........4444444............2/15/12
xxxx........123-456-7890...........1234567............2/10/12
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.
View 5 Replies
View Related
Jul 23, 2007
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.
View 9 Replies
View Related
Oct 22, 2008
I need to enter a formula that calculates the time a report is received from the time it was recorded in our database. therefore, it needs to exclude non working hours. here are the fields:
A1 2008/10/10 16:30
B1 2008/10/11 09:30
C1 8:00
D1 17:00
A1 = report received
B1 = reported recorded in db
C1 = work day start time
D1 work day end time
Where the answer should = 2 hours.
I am not an experienced excel user and so far the only formula I have now is: =TEXT(B1-A1, "d:hh:mm")
And how do I account for weekends?
View 9 Replies
View Related
Sep 29, 2006
Instead of calculating a time difference manually I want to have a formula do it for me. What I want to achieve goes as followed:
08/06/2006 04:33
12/06/2006 01:05
Time difference is 92:32 [hh:mm]
another example:
09/06/2006 12:42
12/06/2006 11:35
Time difference is 70:53 [hh:mm]
So instead of getting 92:32 and 70:53 by calculating it myself I would like to have a formula do it for me. Otherwise I'll have to invest a lot of time to get the information I need.
View 9 Replies
View Related
Aug 20, 2014
After I imported this data, the date and time is in the same column in the format of "mm/dd/yyyy hh:mm:ss" military time. How do I write a VBA code to split up the date and time into two separate columns. One column would only have "mm/dd/yyyy" while the other only have "hh:mm:ss" in military time.
Eventually, I need to extract information from the data by looking for a specific time. I would also plot time vs something.
I don't know if treating it as a string would work, because it would just become a text rather than a time, right?
View 10 Replies
View Related
Aug 10, 2009
I have a cell with both date & time and I need to separate this into 2 separate cells, 1 for the date and 1 for the time. Sample is attached for the before and after formats.
View 3 Replies
View Related
Mar 25, 2014
How to convert this cell date time to 2 separate cells
A1 = 20140320 093000,
to B1 = 2014/03/20 and C1 = 09:30:00 am
View 2 Replies
View Related
Feb 28, 2013
I can calculate total hours when a user enters a start date/time and a finish date/time. The kick is I only want to include hours from 2:00 PM to 12:00 AM (10 hour period). So assuming all the start and end times will be in this range, how can i calculate work hours over multiple days? For example: Start date/time = 2/26/13 2:30 PM and end date/time = 2/28/13 10:30 PM. I want my calculated hours to show 28 hours.
View 1 Replies
View Related
