Time Calculation ...

Jun 4, 2009

Having been looking round this site for quite some time now and always finding what I needed I am now a registered member who needs your expertise.

I have a spreadsheet for which I need to calculate hours worked depending on a few criteria.

[data] ...

The criteria is that Sat/Eve is 8pm to 6am weekdays and midnight to midnight on a saturday. Sun is midnight to midnight on a sunday, BH is a bank holiday and basic is everthing else. What I want to know is it these columns can be populated automatically using formulas.

I would really appreciate it if someone out there is up to completing this challange, as I have to manually populate this at the moment and it can be 5000+ lines long (it takes hours). If i need to change the layout it's not a problem, whatever it takes to automate it has got to be worth the effort.


Time Calculation (Spanning Dates And Using Military Time)

Jul 15, 2014

I have 4 distinct columns.

Adm Date Adm Time Trans Date Trans Time
1/16/2014 937 1/16/2014 1045
1/1/2014 121 1/1/2014 121
1/14/2014 800 1/11/2014 735
1/30/2014 100 1/30/2014 205
1/13/2014 800 1/12/2014 1202

I would like to calculate the difference (# hours spanned from the dates shown). Unfortunately when we transfer the data the COLON is dropped from MILITARY TIME so I am having problems in the calculation. This is also made more difficult as some of the calculates span over two dates.

View 5 Replies View Related

Calculation Of Time With Two Different Time Formats

Jan 30, 2007

Need a point in the right direction with this one! I want to calculate the difference between two time values, the problem is there two different formats as below:-

1) 520.00 - this is a number format

2) 500:00:00 - this is a time format HHH MM SS

What could I use to show I have a 20.00 hour difference???

View 2 Replies View Related

Hh:mm:ss Time Calculation

Nov 5, 2008

i need to total a range of cells, however, these contain time values; hh:mm:ss. it shows me the total when all cells are highlighted. but =sum() doesn't work.

View 2 Replies View Related

Calculation On Time

Mar 1, 2009

I have been burning brain cells trying to figure this out.
I get these numbers from an online source and they come in like this:


The times do not come in as times...when I format the cell to time it doesnt change...that is my first problem.

What I would need to do to these times is: take B and C and find what time is in the middle of them and put that in a different column.

This mess will also need to be plotted on a chart with time by the minute for one day as the X axis. In my example I drew lines on the chart to show what I mean....the blue lines I dont want charted...I use those to find the time in the middle.

View 12 Replies View Related

Same Time Calculation

Feb 5, 2009

I am trying to get a total column that will give the total only when two particular devices are down at the same time. This total will be taken from a long list of downtime entries for different devices but I only want the total when two particular devices are down, for example

Devicedatedowntimedateuptimetotal time
total 1/200:07:00

In the example I am just wanting to work out the total time when both device 1 and 2 were down at the same time, above the total would be 7 minutes because for 4 minutes on the 2/1/9 and 3 minutes on the 4/1/9 they were down at the same time.

View 9 Replies View Related

Time Calculation

Apr 21, 2007

I have a time card sheet that I want to make automatically calculate the time I have worked. It is set up with four columns that can't be changed. The first column is "Time In(TI)", second column "Time Out(TO)", Third column "Time Taken For Lunch(TTFL)", and Finally "Hours Worked". I have no problem getting the formula to calculate the difference in "Time In" and "Time Out" but taking the 30 minutes out of that is causing a rift. If I put the 30 minutes in 00:30:00 format it will take it but it thinks it's 12:30:00 and it displays in 00:30:00 which I want it to display 30. Just to clarify, I am using =(TI-TO)-TTFL in the Hours Worked cell.

View 3 Replies View Related

How Do I Reduce Calculation Time

Sep 12, 2008

I am a big fan of huge files (30MB+) all interlinked with array formulas ...and lots of them. The problem is...one calculation take literally 5 hours. (I have one file I try to calculate since cca 4 hours now...it's at 63%)

I keep getting extra memory with no visible improvement on the processing time. Is there a trick out there that I can use? Something is clearly wrong.

I feel I will soon have to give up Excel, or my formulas, or my job ...

View 14 Replies View Related

Formatting With Time Calculation

Oct 15, 2009

I have a calculation whereby I use two variables which are 1. Man hours left in the week formatted as [hh] and 2. Amount of work left to do in the week formatted as 0" Man Hrs" I need to carry out a calc which says:- Man hours left in the week - Amount of work left to do in the week.

The answer should tell me if I have a deficit or surplus of hours which can then use for planning purposes. Unfortunately the problem I have is that I can't make the formatting the same for both unless someone knows of a workaround.

Man hours left in the week = 128. Man hours required to complete all work this week = 224. Therefore I need to say 128-224 = -96. However when I do this this an answer of 16 is returned.

View 5 Replies View Related

Time Calculation Between Two Cells?

Feb 14, 2014

I have attached excel sheet. Column A consists of List of LAN numbers, Column B consists of List of Documents tagged & Column C consists of time at which particular document is tagged.

I need to calculate the time taken to tag all the documents in Lan1/Lan2/Lan3 in Column D. (I can ignore the date by doing text to columns).

View 3 Replies View Related

Variable Time Calculation

Dec 12, 2007

[code] ......

=SUMIF($A:$A,">="&TODAY()-1,D:D)-SUMIF($A:$A,">"&TODAY(),D:D) calculates the total in Col D looking back from today. The results gives you 12:00. What I need is a formula that will look back from the current time to 24 hours in the past, NOT 1 day, to calculate the total in Col D. Looking back 24 hours, from 10:00am, the results would be 6:00.

Since the first time on 12/12 has not arrive, it will not calculate it, but will look back from 10:00am on 12/12 to 10:00am on 12/11 and calculate a result of 6:00 hours.

View 9 Replies View Related

Time Conversion/calculation

Mar 11, 2008

I need a formula that will calc the following: using Excel XP

8:00 AM04:00 PM 8.00
8:00 AM304:30 PM 8.00
8:00 AM304:30 PM 8.00
8:00 AM304:00 PM 7.50
8:00 AM605:00 PM 8.00

View 9 Replies View Related

Calculation Of Time Formula

Jul 14, 2009

I am trying to calculate the response time between when a phone call comes into my workplace and when the responder calls back...I have created a formula that does this using the times and dates of when the calls were recieved and went back out. This works except when the call comes in on one day and goes back later in the day the next day, making the response time larger than 24 hours. I also have it set up to eliminate 15 and a half hours from the calculation because our place of business is not open during this time. Further details....

The formula currently being used is... =IF(G50=E50, H50-F50+(H50

View 9 Replies View Related

Time Spent Calculation

Feb 3, 2010

I need to find out the total time an issue was 'open' (not resolved) during business hours. Issues come in and resolution time is based off of a priority. Priority 1 should be resolved in one hour, Priority 2 should be resolved in 4 hours, Priority 3 should be resolved in 1 business day (8 hrs), Priority 4 should be resolved in 2 business days (16 hrs), Priority 5 should be resolved in 5 business days (40 hrs), Priority 6 should be resolved in 10 business days (80 hours).

I have start dates/times field in a column (mm/dd/yyyy hh:mm) , a resolution dates/times field in another column (mm/dd/yyyy hh:mm) and a priority in another column (1-6). I need to calculate the time an issue is in open status (not resolved) based on it's priority but I need for it to take into consideration business hours (Monday thru Friday...7 am to 7 pm)...and not calculate time outside of 'business hours'.

View 9 Replies View Related

Textbox Time Calculation

Oct 26, 2006

I'm attempting to calculate the total number of hours between a start time and end time using textboxes on a Userform.

Here is my latest attempt:

Private Sub D24_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myvalue As String
Dim myhour As String
Dim myminutes As String

myvalue = D24.Value

Select Case Len(D24)
Case 1
myhour = "0" & Mid(myvalue, 1, 1)
myminutes = "00"
Case 2
myhour = Mid(myvalue, 1, 2)
myminutes = "00"
Case 3

The above codes are an attempt to force an entry as time (7, 07, 700, 0700=7:00 / 13, 1300=13:00 etc.). However, the result is not correct.

For example:
Start Time in Textbox(D24)=07:00
End Time in Textbox(D25)=17:30
Result in Textbox(D27)=0.4375
Desired Result in Textbox(D27)=10.5

I've never calculated times in textboxes,

View 3 Replies View Related

Subtotals And Time Calculation

Jan 26, 2007

I am trying to calculate the time spent on each task.

I have column A with Task IDs and column B with Time. Column A could have the same task ID repeated but the column B has the time for each entry. There is only start time for each task.

Where the Task is repeated, want to know how many times the task has taken place and overall time spend on one task ID, calculating from the first entry to the last entry.

View 3 Replies View Related

Time Calculation Formula

May 11, 2007

I have attached a time card that needs a formula to calculate the time for the high lighted areas.

In+out-in+out= total hours.

View 4 Replies View Related

Time Zone Offset Calculation?

Apr 2, 2014

I've got a CSV file that has been sent to me where each entry has a vehicle reports in with certain metrics. Included in these metrics are the time the metric is reported. The problem I am having is that the time is delivered in UTC date/time (in Column A). Column B has an "offset" to show how many hours difference there are between the UTC time and the local time for that particular vehicle.

Problem 1 - My initial thought was to use "Text to Columns" with space as the delimiter, thinking I could just run a simple subtraction formula once completed. The problem here is that if an entry is recorded after 7pm local time, the UTC time moves into the next day, and the simple subtraction formula will not take that into account.

Problem 2 - Even if there was a workaround, it seems that the numbers in the "Offset" column don't work well with time calculations because they are not a time (it's -5, -6, -7, or -8).

View 4 Replies View Related

Percentage Increase Calculation With Time?

May 26, 2014

I want to calculate % increase with time related fields

Field A1 = 01:53 (formatted custom as mm:ss) happy to format some other way
Field A2 = 02:08 as above

I want to know the % increase. The correct answer is 13.27% , as 01:53 = 113 seconds and 02:08 is 128 seconds and 128/113 = 1.1327 so 13.27%

View 6 Replies View Related

Calculation Time And Charge Rates

Oct 8, 2007

I am trying to figure out a formula to figure out how much to charge for hrs of a rental. There is an automatic $1000 charge regardless of time used. the rate chart is as follows:

$1000 + hourly charge = total

=<50 hrs= $15/hr
>50hrs but <100hrs= $6.50/hr

ex: so is A1= 200hrs then i want B1 to equal $1820.($1000 + (200 x 4.1))=1820.

I have an idea on how to write the formula, but i am having a little bit of trouble with it.

View 14 Replies View Related

Time Calculation - Ignore Weekend

Jul 6, 2009

Please see attached Excel File.
I would like to calculate Column D depending on below criteria.

1. if both date is same than normal Column C-Column B.
2. if both date is not same than time diifrence should not be count after 17:30 to next day 08:30.
3. I do not want to include weekend (SAT & SUN) time diffrence if both date have.

In column D I have filled two cells that I want by formula.

View 11 Replies View Related

VBA Macro For Time Calculation / Conversion?

May 6, 2014

I am struggling with the calculation of time in excel and cannot find a reliable solution for it using formulas. The reason is, excel does not display negative time values, unless one formats the results as text. This is no good, as I need to add up those results and that's something excel cannot do with values formatted as text.

Therefore I am aiming to do just that by using a macro. The values in columns I & J should simply be converted and pasted into columns U and V respectively (see attachment). For example 200 minutes should appear as 03:20 [hh:mm]. Accordingly, -200 minutes = -03:20 [hh:mm]

View 2 Replies View Related

Time Calculation Spent At Any Area

Dec 27, 2011

How to calculate time spent at an area.

Currently i have in two columns the arrive time and then the depart time. i need to work out how long was spent at the place e.g(10:00am arrive - 11:25 depart time which gives me 1 hour 25 minutes at the point) from there i then need to subtract 1 hour from the 1 hour 25 minutes and then multiply the remaining time (25minutes) by 65

This will then give me the answer 27.08

View 2 Replies View Related

Time Sheet And Wages Calculation?

May 17, 2014

how to further formulate wages calculation.

I made a time sheet which calculates number of hours worked and so on. Now I would like it to calculate the wages as well. The problem is that there is a higher payment for nights.

- $16 per hour on regular hours
- $24 per hour from 23:00 to 07:00

How can I formulate it that I get the total payment taking into consideration nights?

View 4 Replies View Related

Time Calculation With Colored Cells

Dec 29, 2007

I was looking for a solution through the excel questions but could not find anything which would help me.

As you can see in column B are two different colored Sun, red and magenta as well as Mon and Tue.

I would like to find a formula or macro without creating an extra column which looks for the color magenta i.e. if the particular cells are magenta (Sun, Mon and Tue) then perform an action.

I cannot use the formula in K7.

******** ******************** ************************************************************************>Microsoft Excel - Time Calculation with color cells.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE4F4H4K4E5F5H5K5E6F6H6K6E7K7E8K8K9E10F10H10K10K11K12E13F13H13K13E14K14E15K15E16K16E17F17H17K17E18H18K18=
ABCDEFGHIJK1Month: 2Working - Hours 15% 35% 3DayWeekdayStartEndHoursStartEndHoursStartEndHours414Fri20:000:0004:0020:000:0004:00 515Sat0:006:0006:000:006:0006:00 615Sat20:000:0004:0020:000:0004:00 716Sun0:006:0006:00 0:006:0006:00816Sun19:000:0005:00 19:000:0005:009?? 1022Sat20:000:0004:0020:000:0004:00 1123Sun0:006:00 0:006:0006:001223Sun19:000:00 19:000:0005:001324Mon0:005:0005:000:005:0005:00 1424Mon20:000:0004:00 20:000:001525Tue0:006:0006:00 0:006:001625Tue19:000:0005:00 19:000:001726Wed0:005:0005:000:005:0005:00 18 54:00 28:00 22:00Timesheet
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

View 9 Replies View Related

Converting Long Time Calculation To UDF

Jul 18, 2009

I work with a lot of time based worksheets for a 24/7 operation and constantly use the formula:


to calculate times worked where $D17 is start time and $E17 is finish time. Shifts can start and finish the same day ($E17>$D17) or go past midnight ($D17>$E17).

To make matters even more interesting, there are a number of variables that require adding either "and" and/or "or" conditions to this formula so I can end up with several of these nested and it gets pretty complicated.

I'd like to have a UDF so I can just enter the formula:

=CalcTime(Start Time,Finish Time)

View 9 Replies View Related

Time Calculation That Exclude Blanks

Aug 16, 2006

All I would like is to put the result of this Excel formula in the D column:


starting from row 10, provided that both B and C for the row exist (are nonblanks).

View 9 Replies View Related

Calculating Time, Timesheet Calculation

Apr 11, 2007

I am working on a project involving calculating time. It is a timesheet calculation. I was able to design the following layout:

1....Date.........Time IN....Time OUT...Hours... Total
Column A is formatted for DATE. Columns B and C are GENERAL. Columns D and E are DATE format customized as '[hh]:mm'

The formula to calculate the time difference between the numbers in column B and C is located in column D. It is as follows:

View 4 Replies View Related

Calculation Time Not Scale With Worksheet Size

Feb 14, 2013

I have a rather large spreadsheet that takes a long time to calculate. One sheet has the data (about 2800 rows by 650 columns), with samples in rows and data for each sample in the columns. A second sheet has functions to group the data according to predefined patterns. That second spreadsheet is about 800 columns wide and as many rows as I need it to be, as the samples don't need to be analyzed all at once.

Ideally I would like to do all samples at once by making the second spreadsheet 2800 rows high, but the calculation time is just way too long. And there's the problem. The calculation time seems to increase exponentially with the number of rows I calculate at a time. I ran a few tests making it different sizes, and here are the results:




I tried both HLOOKUP and INDEX/MATCH functions and it didn't make a lot of difference. These numbers are for INDEX/MATCH.

Graphing these numbers show that it follows an exponential curve pretty closely. What is interesting is that the progress indicator at the bottom of the screen finishes in a few seconds, about 5 seconds for 350 rows, and I can see the new data after those few seconds. But then it sits and appears to do nothing for the rest of the time. So, for 350 rows, it looks to be done with the calculations within 5 seconds, but then it freezes for another 2 minutes with CPU usage maxed out before I can do anything with the program. That actual calculation time seems to scale linearly with the number of rows I calculate, but the time after it finishes calculating before it finishes whatever else it's doing scales exponentially.

The spreadsheet does have a macro and a couple of other sheets and a macro that do some later analysis and I wondered if that had something to do with it. To check that, I created a new spreadsheet and set it up the same as the original one, with the data in one spreadsheet and the INDEX/MATCH formulas in the other. I copied the data over, but setup the formulas new without copying/pasting anything. It didn't make any real difference.

View 2 Replies View Related

Excel Formula / VBA Code For Time Calculation

Aug 14, 2012

I am calculating the employees time and need the time spent to be categorized as follows

Monday to Friday
06:00 to 09:00 is OT
09:00 to 17:00 is BASIC
17:00 to 00:00 is OT
00:00 to 06:00 is PREMIUM

A2 =Date= 31-07-12
B2 =Start Time = 03:00
C2 = End Time = 23:00
D2 = c2-b2 = 20 hours formatted as [h]

there are situations when end date and time is the next day

E2 = 8 hours
F2 = 10 hours
G2 = 3 hours

07:00 to midnight is OT
Midnight to 07:00 is premium

all the time is premium

View 1 Replies View Related

Time Calculation: Calculate How Many Hours Have Elapsed Between To Entries

Jun 22, 2009

I am trying to build a spreadsheet to calculate how many hours have elapsed between to entries; start time (H10) e.g. 9:15 AM and end time (I10) e.g. 12:15 PM. The formula that I am using in the calculation cell field (J10) is (I10-H10+(I10<H10))*24. This formula works great till I wish to include in an IF statement. What I would like is if the total hours calculated with the formula (I10-H10+(I10<H10))*24 is less than 4, return 4 (hours) otherwise the value. As well if there is no start time nor end time entered then return zero.

View 3 Replies View Related

Time Calculation - Calculate Total Cost Per Job Per Person?

Dec 22, 2013

I am attempting to create a time chart that keep track how long a particular person works on an individual project to finally calculate profit & loss. Attached is the spreadsheet.

I am using invoice #'s to differentiate the jobs but have no clue how to make it calculate how much time was spent per job, per person then calculate the total cost per job per person?

sheets.time chart.xlsx

2014 tab- grey column is installer and white is shop employees
profit loss tab- all calculations done here
payroll tab- all cost data here

View 1 Replies View Related

Date And Time Calculation Giving Incorrect Answer

Nov 21, 2011

The following formula seems to be incorrect. I typed in the times and dates to get the difference in hours, but my times are in 5 minute blocks, so the answer is given doesnt add up

Start DateFinsh date Hours15/11/2011 13:5017/11/2011 2:2536.58

View 3 Replies View Related

Calculation Of Hours Based On Reference (time Format)

Jan 7, 2012

For the past month now, I'm trying to calculate some work hours (night hours actually), based on a reference.

Let me show you the table:

- Column A has all of the schedules of the employees
- Column B has the numbers of hours for each schedule (all have 8.5 hours/day)
- Column C has the reference for the night hours.

Now what I'm trying to do, is to find a formula that will calculate the numbers of the night hours using the reference in Column C

Row 23 in that table has an example of what I'm looking for.

View 9 Replies View Related

Overtime Calculation For Time Worked Over 8 Hr Per Day Or Over 40 Hours Per Week?

Aug 4, 2013

My overtime pays is anything exceed over 8 hours per day or over 40 hours per week. Right now I can only calculate overtime by either over 8 hr/day or over 40 hr/ week. I need a way to combine both.

View 9 Replies View Related

Date/Time Calculation For WeekEnds/After Business Hours

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.


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

Excel 2010 :: Average Calculation For Given Date And Time Range

Jul 9, 2014

Please find the attached 2010 version excel file <For a given date Time Range average .xlsx>.

I want to calculate the average value (Column C) for the given date (Column A) and given time range (Column B).

Values to be plotted in the color cells yellow and light pink / magenta.

Voting Results.xlsx‎

View 4 Replies View Related

Date Calculation Formula - Determine Longer Period Of Time

Aug 9, 2012

How to create a formula to determine the longer period of time between two date calculations and enter the longer period of time in the cell.

My Spreadsheet:
Cell M2: Birthdate (entered in cell as 11/1/2004)
Cell J2: Last date of service (entered in cell as 10/31/2011)
Cell G2: Calculation to enter the longer period of time between (M2 (year) + 21) and (J2 (year) + 7)

Example using information from above:
2004 + 21 = 2025
2011 + 7 = 2018
2025 is the result I want added to G2

If there is any way to include the month/year in G2 that would be ideal.

View 6 Replies View Related

Excel 2003 :: Time Card / Sheet For Semi-monthly Pay Period With Overtime Calculation

Nov 19, 2012

I'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.

Here are the guidelines for the pay periods and overtime:

1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.

2. A work week is defined as Sunday to Saturday.

3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.

4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.

If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.

I'm completely willing to toss out the current method of determining overtime. This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.

Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.

Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.

Excel 2003

Start Work
Time Out
Day Count


View 3 Replies View Related

Retrieving Variable Rows From Another Worksheet: Reducing "Calculation Time"

Aug 10, 2009

I'm using this formula to pick all rows from a huge range of cells "DATA" in another sheet, by matching the first column value (SAPDATA is the range that contains all the first column cells of DATA).


All possible first column values have been stored as a dropdown in cell D4, and whenever I change the choice in the dropdown cell, the retrieval of those hundreds of rows takes ages in excel.

View 9 Replies View Related

Make A Calculation(addition) And Use The Answer To Multiply Against Another Addition Calculation

Nov 19, 2008

make a calculation(addition) and use the answer to multiply against another addition calculation....

The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.

View 2 Replies View Related