I have a spreadsheet with 4 columns - Start Date, Start Time, End Date and End Time. In the 5th column, I need to fill in the "Duration" which is calculated as follows :- Duration = (End Date,End Time) - (Start Date,Start Time)
View 2 RepliesI need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
It's been several years sine I had to look at calculating amount of time worked.
Can you please look at this old spreadsheet of mine and verify that the formula is correct?
It appears to be ok to me, but I don't want any errors when it comes to paying my employees
Formula: ...
Trying to get times worked in hours and mins, from a given start time to a given finished time.
I would like H18 to read 14
and H19 to read 13.5 and so on,
H23 and H24 are full 24 hour coverage,
but i get a 0, when using 08:00 - 08:00
as we only deal with a full or half hours, the figures should read whole numbers or half number as in th example above.
Also would like to total all hours worked in H28 to 117 as opposed to the figure given in the attached file.
DAY / DATESTARTENDHOURS
MON22-May200618:008:00 AM14:00
TUE23-May200617:006:30 AM13:30
WED24-May200617:307:30 AM14:00
THU25-May200618:007:30 AM13:30
FRI26-May200618:008:00 AM14:00
SAT27-May20068:008:00 AM0:00
SUN28-May20068:008:00 AM0:00
TOTAL2.875
Couldnt upload the file as it was 75kbs, a
I need to formula to covert duration between start date and end date to units below.
Start Date
End Date
Number of Month
Number of Years
Number of Days
6/1/13
8/31/13
[Code] .........
I am trying trying to calculate the amount of time students view videos. I understand I should use [h]:mm:ss but excel continues to convert entries such as 0:14:59 (0 hours, 14 minutes, 59 seconds) to 12:14:49 PM both in the cell and the function bar.
The problem gets complicated because I have entered the data with two different formats; some in h:mm and some in h:mm:ss'
1) Is there an easy way to sum the duration time so it accurately reflects total hours:minutes:seconds?
2) Do I need to reformat or re-enter data to make it work?
3) If so, is there a macro to do it?
This has been a bear, (40 students by 30 videos) all hand coded.
I'm trying to create a work sheet with start and ending times with a 30 minute meal break, for the end of the week I would like to calculate those hours as a general number.
View 2 Replies View RelatedI 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 RelatedI need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,
Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)
I am setting up a spread sheet to calculate call duration it would be a great help if data could be entered using one hand IE use decimal point on the number pad instead of the colon is this possible. I will need to subtact start time from end time to get the duration
View 9 Replies View RelatedI'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
I have a data which has a start time, end time, Stay Time(End-Start) and # of subjects. I am trying to create a graph, line, bar or histogram which shows how many subjects were there at a specific time. So the horizontal axis would have time from 00:00 to 24:00 and vertical axis would have the total # of subjects or the %.
Start End Subject Stay
01:00 02:00 1 01:00
01:00 01:45 1 00:45
02:00 21:00 1 19:00
03:10 14:10 1 11:00
The data set is huge(27000 rows) and I am using excel to create a graph.
I have tried using line plots and bar charts(stacked) but couldn't. I am not sure if there is an easier way to do it .
id like a formula that enter "1" into a field if i start after 00:00 and before 03:59 am
so in d4 is my start time and M4 is where i want the formula to go
i tried this formula but did not work
=if(d4>="00:00",<"04:00",1,0)
How do you subtract start/end - dates/times in vba?
I know how to do it with formulas, but can't grasp it with vba. My range will always change, so I'm trying to avoid formulas (Cutting rows etc...)
I need Col D-Col B (end date-start date) and Col E - Col C (end time - start time)
Would doing the calculation on the userform be the best way, or trying to do the calculation with the ws code?
I am attempting to create a datasheet to track the actual time an agent takes a break or lunch. Here is an example of the data that I am using.
name Date Lunch Sch Brk1 Sch Brk 2 Actual Time
BrandonHolt8/19/201411:10 8:30 1:20 8:24:20 Break
BrandonHolt8/19/201411:10 8:30 1:20 11:42:34 Break
BrandonHolt8/19/201411:10 8:30 1:20 12:57:46 Lunch
Because I have two break times, possibility of more depending on the agent, how do i create a formula that will look at this data, take the schedule time for say break one and only apply that against the time that is more in line with the closest actual time?
I'm currently working on a rota for which I would like the worksheet to automatically tell me how many staff members I have beginning their shift before 9am and those finishing after 7pm. I currently have this working via a very crude set of IF statements for each staff member for each day of the week, returning 1 if true and 0 if false. Then I have a sum statement at the bottom of each day. Is there a much tidier and simpler way for me to calculate this?
I've attached the worksheet. A quick note is in Q52.
I am trying to create a time sheet where there will be a start time - end time - number of hours (difference between the 2) - pay rate - total pay, but am getting strange results!
View 9 Replies View RelatedI have a number of processes that I would like to calculate the finish times of.
For example, Process A may take 18 hours to complete, Process B may take 28 hours to complete and Process C may take 125 hours to complete etc
However these processes only run during certain times, i.e. 9am to 5pm.
Ideally I would like to setup a spreadsheet that when given the start time and process time calculates the (date and) time the process will finish.
I can get it to work on a 24 hour day but I've been tearing my hair out trying to take out the none-working part of the day (the 5pm to 9am).
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I have various start times for employees.
I need to find out the total movement in start times per week for the employees.
I have used MIN and MAX to find the earliest and latest start times, and then a subtraction to find the difference.
This works brilliantly until my employees who start around midnight.
If they have started before and after midnight, it takes midnight as the earliest time and 23:00 as the latest, giving a difference of 23 hours instead of 1 hour.
i.e.
Start Times
23:00, 23:15, 00:15, 00:30
Range of Movement
00:30 - 23:00 = 01:30
I've tried, =$B$9+($C$4>$B$9)-$C$4 but this does not work when the MIN reads 00:00.
I have been using this statement
if(and(c1>=a1:a144,c1<=b1:b144),"yes","no"))
And it just works for the first 2 values c1, c2 and doesn't fit for the others.
The case is i have more than one event at the same video and i need to confirm that no event was taken unless it is between start and end.
Here are some samples:
Start dtime End Dtime Event Dtime
16/09/2013 22:13:34 16/09/2013 22:14:18 16/09/2013 22:13:38
16/09/2013 22:15:57 16/09/2013 22:24:30 16/09/2013 22:16:02
16/09/2013 22:24:30 16/09/2013 22:33:49 16/09/2013 22:17:32
16/09/2013 22:33:53 16/09/2013 22:35:05 16/09/2013 22:19:02
16/09/2013 22:35:05 16/09/2013 22:39:57 16/09/2013 22:20:02
So as you can see there are more than one event between one start and end dtimes.
I have been using this statement.
if(and(c1>=a1:a144,c1
I am trying to come up with a formula that calculates total time someone has worked in a day. The scenario is an individual will work at a home and start working with an individual. Their start/end times look like this in a pivot:
Min Start Max Start Min End Max End
Location A+Counselor A 8:56 AM4:01 PM 1:11 PM 7:00 PM
Location A+Counselor B 12:00 AM 8:00 PM 6:00 AM 11:59 PM
Location B:Counselor C 7:00 AM 12:00 PM 2:00 PM 4:00 PM
Location C+Counselor D 8:00 AM 8:00 AM 4:00 PM 4:00 PM
Some people work split shifts while others work a straight shift. The formula I created was this:
=IF(OR(B9=C9,E9=D9,D9=C9),E9-B9,IF(D9>C9,((E9-D9)+(C9-B9)),IF(C9>D9,((D9-B9)+E9-C9),"New Formula Needed")))*24
(I use a pivot table to show max min for start and end times)
This works great except for the individuals that have multiple punches during the same time frame. The one scenario I am having trouble solving for is when someone punches in more than once during their shift displaying. This occurs when a counselor starts a shift working with one person but then adds another person mid shift. An example of this could be:
Location A+Counselor E Min Start Max Start Min End Max End
Consumer 1 1:00 PM 1:00 PM 8:30 PM 8:30 PM Total Time: 7.5
Consumer 2 12:00 PM 12:00 PM 2:35 PM 2:35 Pm Total Time: 2.6
Pivot says that they worked a total of 10.1 because it is grabbing the max and mins and calculating. The actual total time worked is 8.5 hours in reality.
The raw data comes in like so:
Location Counselor Consumer Start Time End Time
A A A 1:00 PM 8:30 PM
A A B 12:00 PM 2:35 PM
A A C 12:00 PM 5:00 PM
Is this solvable with a formula?
I want the start and end times of the shift to turn black or red depending on whether the adjacent cell says "off" of "Hol" respectively. I have this working except for when I actually enter smething into these cell ie a shift, the cell turns black.
View 4 Replies View RelatedI have 2 dates+ times.
1 the contract start date and time.
2 the contract end date and time.
Can I calculate the number of working hours between these two using a formula?
So if I have a 10 hour working day (08:00-18:00), Mon-Fri and the two Dates/Times can I calculate the number of working hours?
Example
Start 06-Mar-07 10:00
End 14-Mar-07 14:00
This is 2 part days and over a weekend.
I need a formula to calculate a date 6 months forward from the start date. However, in the calculated 6th month, I need it to the day before the start date.
ie: start date: 26/12/13 -- End date: 25/06/14
if I've worked in the company for 9.0384 years, how can I calculate that my start day was 04/20/04?
View 3 Replies View RelatedI have 2 dates a Start Date and an End Date.
I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column.
Start End Yr1 Yr2 Yr3
11/01/99 11/01/00 11/01/01
10/31/00 10/31/01 10/31/02
Col A Col B Col C Col D Col E
10/31/00 07/05/01 365 117 0
I thought a series of IF statements but I am open to an easy solution...
I have a column that is a percentage(p%) which measures the fullness of the location(loc) that the item(ite) is in, which also has a corresponding location(loc) and item column(ite).
What I need to do is add as many items(ite) to one location(loc) as possible without exceeding 100%(p%), however if the value exceeds 100%(p%) then we need to start all over in a new location. The end result would be an unknown amount of unused locations.
If p% < 100 then add the next row's p% value, else start over with a new location. I am trying to add as many items to one location as possible, and once a location fills up, we will use the next one.