Calculate Work Hour Phone Time By Day, Week & Month
Aug 21, 2008
I am trying to figure out a way to count the minutes used from 8am-5pm Monday-Friday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.
View 2 Replies
ADVERTISEMENT
Apr 27, 2014
Formula to calculate time allotted minus time used and show the difference in hour and minute.
View 1 Replies
View Related
Jul 21, 2014
I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.
Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.
For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.
here is the distribution of columns per week as reference.
(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns
(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns
View 1 Replies
View Related
Apr 10, 2014
Time arithmetic, I have two cells representing a time range.
The first one (say: X1) is formatted using the custom format [h]:mm and contains a certain number of hours and minutes. It gets its value by summing up other cells in the same format. A typical entry could be 98:35 to represent a duration 98 hours and 35 minutes.
The second cell (say: X2) is formatted as a number with 4 decimal places after the comma, and similarily gets its value by summing up other cells in the same format. It also represents a time duration as a number of hours. A typical entry could be 202.7500 to represent a duration of 202 hours and 45 minutes (because 0.75 of an hour is 45 minutes).
I would like to calculate the hour difference between these cells, and display it as hours and minutes. In the example given, the result should be negative, i.e. -104:10.
My first approach was to use the formula X1-X2 and format the result as [h]:mm, but this gives me a #VALUE! error.
View 2 Replies
View Related
Dec 30, 2013
I would like to calculate the week number of the month based on a date.
Now my days would only include working weeks (Monday - Friday).
Supposed the date is 12/31/2012:
M
31-Dec
T
1-Jan
W
2-Jan
TH
3-Jan
F
4-Jan
Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:
M
28-Jan
T
29-Jan
W
30-Jan
TH
31-Jan
F
1-Feb
It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:
M
29-Apr
T
30-Apr
W
1-May
TH
2-May
F
3-May
It will be considered as Week 1 of May since it occupies only 2 days of the working week.
Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.
View 4 Replies
View Related
Dec 31, 2013
In one column I'll have a list incrementing in 1w,2w,3w,1month and I want to be able to count the number of days that have elapsed till the latest cell. Right now I'm just winging it by saying there's always 31 days in one month using a COUNTA function, but I need it to be accurate.
View 9 Replies
View Related
Mar 16, 2009
I would like to find out if a job took more than 24 hour cycle time (eg. 6:00 am to 5:59:59 AM next day). data:
A1 = received date & time (format "m/d/yyyy h:mm")
B1 = completed date & time (format "m/d/yyyy h:mm")
my formula is '=if(B1-A1>"24:00"+0,"Yes","No"). The problem with the formula is that it doesnt exclude weekends nor public holidays. I couldnt formulate a solution using networkdays function......
View 3 Replies
View Related
May 17, 2014
I found this formula similar to other formula
NETWORKDAYS(D1,F1)-1)*"18:00"-"9:00")+IF(NETWORKDAYS(F1,F1),MEDIAN(G1,"9:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS(D1,D1)*E1,"9:00","18:00")
By the way ....D1 = start Date
F1 = End Date
E1 = start Time
G1 = End Time
It's GREAT! But now I need include time from 6:00 a. m. to 10:00 p. m. (I already modified your formula:
(NETWORKDAYS(D1,F1)-1)*("22:00"-"6:00")+IF(NETWORKDAYS(F1,F1),MEDIAN(G1,"6:00","22:00"),"22:00")-MEDIAN(NETWORKDAYS(D1,D1)*E1,"6:00","22:00")) because are regular working hours in Colombia but:
1. All the days (Monday to Saturday) are working days
2. I just want to calculate the time in shifts, I don't specify the start date and end date. Example: 8:00 p.m. to 11:00 p.m. Result: 2 hours, because 1 hour is after 10:00 p.m.
This are two different options, so I need two separate formulas.
View 10 Replies
View Related
May 27, 2014
1. Calculate the time that has elapsed between 2 times in both hours:min (hhmm) and total mins (mm)
2. Compute the day of the week (mon-fri) a particular date fell on. I really only need to know if the date fell on a weekday or weekend.
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= M-F
2=S-S
3. How to write an If statement that assign a value to time based off this chart:
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= AM (7-1459)
2=PM (15-2259)
3= MN (23-659)
View 9 Replies
View Related
Sep 4, 2006
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.
My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.
View 9 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
Aug 10, 2009
I have a simple VBS script that puts the username & current time in columns. When the user saves that time is also placed into a column.
I would like to be able to calculate the amount of time a user has spent on the spreadsheet for the current month & if possible the total time all users have spent on the spreadsheet this months.
View 8 Replies
View Related
Mar 7, 2008
I'd like to convert from 24 hour standard time to 12 hour time using VBA code. For example: instead of 13:00 I need 1:00.
View 9 Replies
View Related
May 8, 2008
I have a list of FLIGHT departure times that are listed in MIL TIME, however, there is no : in the format. Its just 4 or 3 digit numbers. I need to convert these to time in 12-hour clock. If I go to FORMAT/CELL/TIME and select 1:30pm it simply makes the time ZERO!
View 9 Replies
View Related
Jun 16, 2014
I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.
I'm using weekday formula but no luck.
View 6 Replies
View Related
Dec 28, 2006
I am building a spreadsheet to manage 15 folks wages to be able to know how much money has been spent.
We routinely work 40 hour weeks and have a sheet that automatically multiplies hour work by person (Say A1) times their hourly rate (say A2)
My question is how could I right it a formula that allows me to type in their hour work (A1) if it's greater than 40 hours?
Say A1 is 40, A2 =$20.00, A3 =(A1*A2) $800.00
If the A1 is 43 what would I put into A3 that would automatically multiply the additional hours over 40 times 1.5 A2
View 9 Replies
View Related
Nov 24, 2009
I have a column where I am convering the Date into a Fiscal week number.
For example 10/6/2009 is Work week 41
Now I want to show October Week 41
I need to add the month and the text "Week" before the week number. what is the formula I use.
View 3 Replies
View Related
Mar 3, 2009
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
View 9 Replies
View Related
Sep 11, 2013
In A1 I have 35 min elapsed time and need to change it to time as a portion of an hour. It reads the 35 min as TIME, therefore I am currently using
=IF(A1>=0.5,A1-0.5,A1)*24. Output is .58, which is perfect.
(output column formatted as a number)
Likewise 3:28 becomes 3.47.
View 1 Replies
View Related
Dec 20, 2007
I have downloaded .csv files with my phone call costs. I want to compare phone companies, so I need to calculate the h:mm:ss amounts to $ based on flagfall and rate per 30 seconds. I can open the .csv files in Excel but I do not know anything about Excel.
View 4 Replies
View Related
Aug 10, 2006
I normally use the WORKDAY function in order to create an involved scheduling
program. However, I have no idea how to create a formula that would count
Saturdays as a workday. I usually type in one date and then the dates kick
out to an end date several months later. How do I create a formula to
include Saturdays on a daily projection (e.g. A1=8/12/06, A2=8/14/06,
A3=8/15/06,etc...)?
View 9 Replies
View Related
Mar 26, 2008
I have a range of data which is as follows:
Week in month: 1 1 1 5
Site: 01/03 02/03 03/03 etc 30/03 etc
Leeds 10 9 15 20
Manchester 8 5 1 2
Etc
Here's what I need to produce:
March 08 April 08
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6
Leeds
Manchester
I need to sum week 1 to 6 for each month Mar, Apr and so on. The different sites are in the same order so that doesn't matter too much.
View 14 Replies
View Related
Dec 20, 2007
I need a formula to calculate the time (in hours/fractions of an hour) that is "covered" for each hour of the day between a range of times. In other words, I have a "START TIME" and an "END TIME" and for each hour of the 24-hour clock, I want to know how much time this range covers.
For example, if my start time is 3:30am and my end time is 5:15am, for the 3am hour, the formula would return 0.5 hours, for the 4am hour it would return 1.0 hours, for the 5am hour it would return 0.25 hours, and for all other hours it would return 0.0 hours. The range of START TIME and END TIME can be up to 24 hours (but not more), but the tricky part is that the START TIME can be on the day BEFORE the END TIME (e.g., START TIME of 10:35pm and END TIME of 5:45pm the next day).
View 9 Replies
View Related
Mar 13, 2008
I found a formula for calculating time in the HH:MM AM/PM
=IF(B1<A1,B1+1,B1)-A1
This formula was to give me total hours in the cell for which it is entered, and cell format for the formula was in military format.
I cannot find this post. The formula worked in OpenOffice Calc program, but when it came to converting to excel, I came up with #value!
I want to enter the time in 12 hour format and using am/pm to designate. I am making it for someone to make work schedules with and they do not know military time.
View 11 Replies
View Related
Apr 19, 2014
I have a spreadsheet that I use to track my stocks. I have installed a third party add-in that goes out to the internet and grabs various stock information. But, when you are pulling info on about 30 stocks and getting info like name, price, dividend, div pay date and such, it slows the spread sheet down tremendously. Is there a way to keep the value in the cell and change it only during a certain time period such as 5 minutes before the top of the hour to five minutes after the top of the hour.
View 2 Replies
View Related
May 28, 2008
I have made a form with a calender control, a keypad for hours and minutes. I have no problem getting the date from the active cell into a label caption and changing it by the calender, but I am having trouble reading just the hour and minutes into separate label captions from the active cell and changing them via the keypad and updating the active cell at the same time with a new date and time.
View 3 Replies
View Related
Jan 7, 2014
I am trying to calculate the the breakdown time for each hour in the factory. I have the start time and finish time of each breakdown. There are 8 hours in a shift starting at 6:45. So hour 1 would be (6:45-7:45), hour 2 (7:45-8:45). If the machine breaks down between 7:06 and 8:20 what will be the amount of time lost in each hour? In this example it would be 39 mins in hour 1 and 35 mins in hours 2. How I can achieve this result?
View 4 Replies
View Related
Feb 15, 2008
I am trying to calculate downtime for a Service Level Agreement.
The data that I have is the start date/time and the resolved date/time for an incident.
The data are in the format - 1/1/2008 03:32 AM. The incidents may occur at any time but downtime is calculated only business hours and excludes weekends. I may be required to exclude holidays but that is not a hard requirement right now. What I must be able to specify are the working hours.
As example(s)
- if an incident is generated on 1/2/2008 4:00 PM and resolved at 1/3/2008 11:00 AM then the downtime is 4 hours.
- if an incident is generated on 1/12/2008 4:00 PM (which is a Sat) and resolved at 1/14/2008 11:00 AM (which is Monday) then the downtime is 3 hours.
The above assumes working hours are 8:00 AM through 5:00 PM.
I have tried using the NETWORKDAYS and WORKDAY functions with little success.
View 9 Replies
View Related
Jan 28, 2010
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
View 9 Replies
View Related
Jul 1, 2009
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).
Jan-09
Feb-09
Mar-09
Apr-09........
View 9 Replies
View Related
