Calculating Time And A Half And Double Time (After 40 Hours)

Feb 20, 2009

I'm battling to come up with the formula to calculate time and a half and double time. My boss wants me to show the overtime worked AFTER 40 hours has been worked. I have attached a copy of my spreadsheet.

I can't figure out how to do a proper formula for calculating time. For instance I have one column that says "Time In", the next is "Time Out" and the other is "Lunch Time". My calculation needs to be this:

Find the total hours between the Time In and Time Out and then Minus the lunch time to get total hours worked for the day. The only way I can get this to work is using 24 Hour time format. Is there another way?

I’m working on a timesheet and I need to separate the hours worked that are before 6 am from all the others. For example if someone works 1:00 am to 8:00 am I need a cell to populated with 5 representing the hours worked before 6 am. The formula below works fine except when the start time is 12:00 am. I am also having trouble if the start time is before midnight like in a 11pm to 5 am shift.

I am using Microsoft Excel 2003. My question is about calculating time. Let's say my answer, after calculating time, is 2 hours and 1 minute (2:01) and that answer is placed in cell A1. How would I be able to have only the 2 hours (2:00) carry over to cell A2 without the minutes showing?

I have tried =MOD(A1,24/24) and =TIME(0,HOUR(A1),0). I have also tried to right click the cell, went to Format Cell, went to the Numbers tab and tried the different options in the Time and Customs category, but I can't seem to get 2:01 to appear as 2:00 only.

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.

We have a person who work in 24/7 support. One person work passive 24 hour, but it 24 hours are divided in to next shifts: normal 06-18, over hours 18-22, night hours 22-06.So if we have some one who is supporting client say from 17h till 23h, we have next results:

Name Start time End Time 06:00 - 18:00 18:00 - 22:00 22:00 - 06:00

John 17:00 23:00 1:00 4:00 1:00

Is it possible to in time range enter formula who subtracted end time from start time, but taking into account 24 hours?

If a Rescue Officer is called out at 23:00 and is back at 04:00, this should equate to 5 hours worked.

It seems that if my times are all on one side or the other of a 24 hour cycle, my calculation work fine but it it breaks across the 24 hour (as above, it doesn't work.

A2=04:00 A1=23:00

Using (A2-A1)*24 give me -19.00 hours

My SS macro has a line:

s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked

Is there any way of getting excel to calculate an elapsed time in hours when the start and end times roll over from one day to the next?

I want to set up a spreadsheet that can show my work is completed within a service level agreement, based on working hours of 9-5 Monday-Saturday.

I want to be able, for example, to log that a piece of work is reported at 09:00 on a Monday and completed at 10:00 on Monday and for excel to calculate that as 1 hour until completion. Fine so far. But what if that job was closed at 10:00 two days later? Based on an 8 hour working day, that should be 17 hours.

And what if a bit of work comes in at 17:00 on the Saturday. No one works the Sunday. Say the job is completed at 10:00 on the Monday. that should calculate as 1 hour to completion.

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.

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?

I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!

For example Start time: 21:00 Finish time: 06:30 Hours worked: 9.50

What I need is a formular/vba code that can divide hours into different time ranges. The picture below shows an explanation.

In example 3 there time range analyzed goes from saturday till monday. Here Sunday should get 24 hours and the time range 23-08 for weekdays is at this point 00-08.

I've tried everything I could imagine to get this one working,

I have this spreadsheet where it is basically call center data so in one of the column it shows time of inbound call as example (21:12:05) and other columns shows specific notes about that call example if product was sold on that call or not. So I am trying to find trend that during what period of time in a day maximum product was sold. Example I want to set some criteria that from 8 am to 10 am, 10 am to 1 pm and so on and then to find how many products are sold. So what I am looking is how can i extract hours as numbers only? from time stamp? I tried using =RIGHT(a1,2) but its not working and it messing with the number format.

I am having a little trouble with a spreadsheet I am creating, the formulas and cell formatting I should be using to enable this to work.

Here goes:

in cell E3 I have a time started (e.g 12:45 pm), In F3 i have time finished (e.g 2:30 pm)

So, what i'm wanting is the time taken in G3 and also, i have the amount of units that is entered manually in H3. I would like I3 to show the time taken per unit.

I know the title is a bit vague, but I cant think how else to word it!

I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target.

I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.

We have a bar code scanner that scans employee IDs as they come and go each day. Beside the ID is the date/time stamp in the next column. They do not always come in in the same order they leave, so I am looking for a solution that will collect the in and out time for each ID, then add those times beside each employee name, and add them for a hourly total.

I have a started time of say 8am from cell B3. I want to know what time it will be when I add a full number (hours) from another cell. For example A3+B4 where A3 is 8:00 A.M. and B4 is 4.04. I'm looking to get the a result of 12:04 P.M.

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?

I m Trying to add some features to it and run into an issue. Here are the fields we are working with!

A1= 13:30:00 17/12/2008 (in other words 1:30pm on date given) - Need to know proper cell number format!!

B1= 23:00:00 (this represents 23 hours) - format for cell is [h]:mm:ss

C1= Unknown function (This field is my problem!!) Answer here SHOULD be "14:30:00 16/12/2008"

I need a function for C1 (using A1 and B1) that will give me the time and date 23 hours earlier or however many hours:mins:secs is specified in B1! Also need to know what number format to use for both A1 and C1

I have a timesheet which for certain days of the year calculates double-time. What i would like to do is have a formula that gives double time for a 24 hour period. I have a formula setup with a start time and end time for my double time, but it wont stop at midnight.

I'm trying to have excel insert a timestamp on a specific range of cells. I have it currently working on one cell, but can for some reason am having trouble getting it to work on the entire range... Here is what I have currently...

PHP Code:Â

PrivateÂ SubÂ Worksheet_BeforeDoubleClick(ByValÂ TargetÂ AsÂ Range,Â CancelÂ AsÂ Boolean) IfÂ Target.AddressÂ =Â "$E$23"Â Then Target.ValueÂ =Â Time EndÂ If EndÂ SubÂ

I would like this to work for the range E23:E28 and F23:F28 I'm sure its something simple that I'm not doing...

I am using the below code to pick folder to get that name in text box. But when I click cancel in folder picker dialog box it disappears and again appears. Then again I have to click cancel. Am I using "If .Show = False Then Exit Sub" in correct way?

VB: Private Sub DirListText_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim FolderPath As String With Application.FileDialog(msoFileDialogFolderPicker) .Show If .Show = False Then Exit Sub DirListText.Text = .SelectedItems(1) End With End Sub

calculate the full-width half-max of a peak in data? My chart (gray value vs. pixels) essentially looks like a letter M. What I've tried is finding the max of each peak, dividing that by two, and then using vlookup to find the corresponding pixel values on each side of the peak. But, since vlookup does not work for descending values, I cannot find the half-max value on the right side of each peak.

I would like to be able to track progress of various tasks/occurences by allowing users of a spreadsheet to just click in the appropriate column and in doing so, it would fill in the current time and date. I.E. Column headings would read, "Item Sent to Review", "Item Received From Review", "Item Sent to Specialist", etc. and I want users to be able to double click in a given column to populate with current date/time information,

I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?

Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6-AC6 divided by the frames, every time I try it I get a messed up answer.

=INDEX({"$5.00","$3.00","You Were Late"},MATCH(L17,{7:00am,7:05am,7:06am},1))

i have this formula in excel...this formula sanrv1f posted to help with another question (values changed) but i thought i would work with what i wanted but i get a (Value) error

so what im trying to do is base on the system time is if the person typed in

if they were early 6:45am to 7am they get Extra $5 on top of the $5 they get for being on time.

6:55am gets $5 7:00am they get $5.00 for being on time if they type 7:01am to 7:05am they get $3.00 if they type 7:06 to 7:10am would return You Were Late after 7:10am would return No Pay

so Ex

D4 *answers in E4 *

7am would get $5

7:03am am would get $3

7:07am would get You Were Late

7:11am Would get No Pay

if they were on time 5 times (ie 5days in a row) they would get a bonus of $20 i tried factoring this in could not do it :/

I am trying to create a spreadsheet to determine hrs per time interval

i.e 06:00 - 14:00 14:00 - 22:00 22:00 - 06:00

So a start time of 06:00 and finish time of 14:00 would show 8hrs in first interval and 0 in the other 2 and a start time of 10:00 and a finish time of 18:00 would show 4hrs in first interval and 6 in second and 0 in last

I've currently got start time in A1 , finish time in B1 and want hours for interval 1 in D1 , interval 2 in E1 and interval 3 in F1.

I'm trying to use conditional formatting to highlight phone calls that came in between certain hours.

The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.

Example:

If the call came in between 22:00 and 23:59 color is light green. If the call came in between 23:59 and 08:00 the color is yellow.

I'm creating a calculator to determine the distance & travel time on a xy grid for a game. I'll share what I have so far to display the big picture. Here’s what I’ve accomplished so far:

#1) Cells A1 & B1 are dynamic, they are frequently changed so distance and time may be displayed. Cells A2 & B2 do not change.

#2) I use the following formula to determine the distance between cells, displayed in C2: =SQRT((A2-$A$1)^2+(B2-$B$1)^2)

#3) Next I multiply C2 by a number so that distance is measured correctly per unit type for this game. (I hide this column). Therefore, cell D3 has the following formula: =C2*20

#4) Now my problem, cell E3 - Correctly displaying time. I have the following formula in E3: =TIME(0,D2,0)

This works great!! That is until the time goes beyond 24 hours, then it rolls over. I've tried right clicking and setting the custom format of E3 to [hh]:mm, it still rolls over. I’ve also tried setting formatting to d:[hh]:mm, if I do this then the output time displayed is incorrect.

Ideally I would love a simple fix so that the time will roll over and show days. (I would like a display similar to d:hh:mm).

I’ve done a fair bit of research online and either I'm missing something simple or this is more complex than I thought. And not to make this any more confusing, but I would love whatever I accomplish here to be useable in compatibility mode ...

Basically I want to be able to type a number and have a colon inserted two spaces to the left. So 123 would become 1:23, 1234 would be 12:34 and 12345 would be 123:45.

I'm working with anywhere from one to several thousand hours in a spreadsheet. I'd like to be able to use a time mask like this: