# Calculate Start/End Times

Dec 20, 2006
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

View 9 Replies
ADVERTISEMENT
Jan 21, 2008

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 Replies
View Related
Feb 14, 2008

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: ...

View 4 Replies
View Related
Mar 7, 2009

I 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)

View 9 Replies
View Related
Apr 8, 2014

I'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.

View 7 Replies
View Related
Jul 8, 2014

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 .

View 4 Replies
View Related
Oct 7, 2006

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)

View 9 Replies
View Related
Dec 8, 2009

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?

View 9 Replies
View Related
Aug 21, 2014

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?

View 1 Replies
View Related
Sep 20, 2013

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.

View 3 Replies
View Related
Apr 30, 2009

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 Related
Feb 22, 2010

I 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).

View 9 Replies
View Related
May 13, 2014

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.

View 2 Replies
View Related
Sep 25, 2013

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.

View 4 Replies
View Related
Sep 25, 2013

I have been using this statement.

if(and(c1>=a1:a144,c1

View 3 Replies
View Related
Feb 12, 2014

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?

View 1 Replies
View Related
Oct 13, 2009

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 Related
Mar 22, 2007

I 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.

View 9 Replies
View Related
Dec 25, 2013

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

View 3 Replies
View Related
May 2, 2013

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 Related
Sep 4, 2008

I 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...

View 9 Replies
View Related
Aug 9, 2007

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.

View 6 Replies
View Related
Jun 11, 2009

As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?

For example,

Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009

Start Date: ???

Finish Date: 8 Jun 2009

Duration: 5 days

The Start Date should be 30 May 2009.

Seems like I am not able to use WORKDAY function to calculate the start date.

View 11 Replies
View Related
Feb 27, 2010

Is it possible to calculate hours if both the start and end time are in the same cell. Unfortunatly the developers of our scheduling program developed it so when you export the schedule to excel it shows Greg in A1 and 3:00pm-9:00pm in A2. I would like to have excel calculate the 6 hours so I can use it to auto generate another spread sheet I am making.

View 10 Replies
View Related
Dec 9, 2007

I'm trying to create a simple sales report. No VBA code, only excel formulas.

I'm stuck on trying to calculate the weekly sales. I want excel to be able to recognize the day of the week and know that the month started mid week.

Ex. If the 1st of the month started on a Wednesday, it adds all the sales from Wednesday to Saturday only and

if the month ends on a Tuesday, it will calculate the sales from Sunday to Tuesday only.

I want it done automatically.

I've included a zipped excel sheet example of the worksheet for a visual example.

View 9 Replies
View Related
Mar 13, 2014

I have a small project at work where I am being asked to put a simple spread sheet that will calculate a start/stop time - and also include the date. For example:

Start time 5pm, date: 3/13. The spread sheet to auto calculate what the stop time and date will be if a specific amount of hours is to be calculated. For example in this case, 12 hours. From calculating in my head that would be 5am the following day. However, how can I get this in excel to work and therefore all i would have to do is enter the start time and date, + 12 hrs, and excel would calculate the time/date after the additional 12 hrs.

View 3 Replies
View Related
May 15, 2009

I am working on a sheet that will allow a user to enter a start month (from a pre-defigned drop down list) and an end month (again from ddl). What I need to calculate is in what months the employee worked in so I can calculate their salary cost by quarter.

It can populate other cells in order to do the calculation if needed. So, for example if an employee started in May, and worked until December, I need to calculate that he worked 2 months in Q1, 3 months in Q2, 3 months in Q3, and 0 months in Q4. Our fiscal year is April to March.. ;-) I have thought of several options, but none of them have worked 100%.

View 2 Replies
View Related
Dec 13, 2013

test.xlsx

I need to calculate the time elapsed between start and finish dates. I've figured the TODAY function, as explained here but I'm referring solely to dates in cells. How to?

View 1 Replies
View Related
Mar 6, 2014

I am trying to calculate the average start time for a machine over a period. However the machine start time varies from 10:00 pm to past midnight. I have tried reviewing past posts but cannot seem to find a similar query.

View 13 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
Sep 16, 2009

I'm trying to combine monthly calculations with "today" and with "workdays"

Example:

start date = 01/01/2009

today's date 09/16/2009

formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)

=edate gives me a month but it doesn't skip weekends or calculate beyond today's date

View 14 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
Jan 2, 2014

This is for employee time sheet and schedule. I have multiple employees work a specific shift on various days. I need the calculate the hours worked each day and then have them totaled at the end of the period. Here is and example:

Employee Monday Tuesday Wednesday Thursday Friday Saturday Sunday Total Hours Total Cost

John Smith 10-5 12-6 9-2 5-10:30 3-8:30

How do I get it to calculate the difference in the same cell, convert it to hours, add up all the results and place it in the totals columns then calculate those totals by their pay rate to get my Labor cost?

View 1 Replies
View Related
Sep 12, 2007

I need to calculate turn around time between two sets of data reflecting both a starting date and time and obviously an ending date and time. This should reflect work hours meaning a day that starts at 08:00 and end at 16:30.

That would be a start and would be great if someone can show me how to calculate this.

But obviously people don't work on Sundays and only work from 08:00 to 12:00 on Saturdays... Can't even imagine how one would take this into account.

View 11 Replies
View Related
May 19, 2008

I seem to have the worst luck when it comes to getting dates and times to calculate together. My logic just seems to go straight out the window.

What I am trying to do, the end result, is get a Time Worked (duration) that I have spent on a ticket, and a Total Time, the time a ticket was opened, to the time it was closed.

I guess we can try to do this one step at a time.

The first thing that I would like to know is if there is a way to calculate Date/Time in a MM/DD/YYYY, H:MM format?

IE:

Ticket Opened ...................Ticket Closed

5/16/2008 2:54 PM..............5/19/2008 10:47 AM

Total Time in Days / Hours / Minutes

Right now I have the Date and Time split into separate cells, simply because I couldnt figure out a way to subtract the combined date and time.

View 10 Replies
View Related
Sep 11, 2009

I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.

For example:

I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.

I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.

I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).

View 14 Replies
View Related
Sep 2, 2008

I'm not too hot with Excel formulas but I'll explain as best I can. I have 3 columns with dates and times

A - 01/08/2008 08:17:08

B - 01/08/2008 12:17:08

C - 01/08/2008 17:00:11

This is what I want to do...

If A > B then Calculate the time difference between A and C

If A <= B then Calculate time difference from B to C

I'd like to get this formula in 1 cell if possible?

View 4 Replies
View Related
Jan 25, 2012

From the below example, I would need to know the difference between date & time of Received and Resolved in number of days. Meaning - time difference should also be calculated in days

CELL ACELL BCELL CCELL DCELL E - REQUIRED OUTPUTReceived

DateReceived

TimeResolved

DateResolved

TimeTurn Around Time - IN DAYS20-Jan-122:43:00 PM23-Jan-124:50:00 PMDifference between Resolved date & time and Received date & time

View 7 Replies
View Related
Jun 22, 2012

Is there a way to calculate the number of hours between two times specified like this:

Start

End

Hours

Comments

Mon 6:00p

Mon 9:00p

3.00

Same day

[Code]..

I think I can write a UDF and that may be the easiest. Is there a straightforward way without a UDF?

View 6 Replies
View Related
Jun 13, 2013

I have a huge two column spreadsheet. The first column is a list of times in the format: dd-mm-yy hh:mm:ss and the second column is a status of either START or STOP

Every time there is a START, I want it to count the difference between that time, and the next STOP time, and display the results in column C

Sometimes there will be multiple START's in a row or multiple STOP's in a row, but I am only interested when the status actually changes from the first START to the first STOP. It then resets until there is another START.

Here's an example of how I'd want it to function:

13-06-13 01:22:13 START

13-06-13 01:22:16 START

13-06-13 01:22:20 STOP 00:00:07

13-06-13 02:20.01 STOP

13-06-13 02:21.03 START

13-06-13 03:22.04 STOP 01:01:01

I can do this manually, but of course there is too much data to do that, so I'm looking for an automated solution that looks at the status.

View 1 Replies
View Related