Trying To Add Hours - If Function

Jun 11, 2008

I am trying to add four hours to a time. That I know how to do, but there is some added logic.

If I put a value in the first column I want to add logic to make the values in second column appear. It is basically adding four hours to a time value unless it is after 5pm, before 8am, etc. This partially works, but doesn't count for the 2pms, 3pms, 4pms, etc.

=IF(OR(G16>0.708333333333333,G16

View 9 Replies


ADVERTISEMENT

Separate Simple Hours / Holiday Hours And Night Hours

Jun 27, 2014

As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.

Nights, holidays and holiday night have different price/hour.

The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for me…

On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!

Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)

View 1 Replies View Related

Function Decimal To Hours

Mar 27, 2007

I'm trying to do is to convert an hours per week field as a decimal into hh:mm as text. For example, 22.8 would convert to 22:48.

Public Function DecToHours(Ldecimal1 As Long)

Dim Ldecimal2 As Long
Dim Shours As String

Ldecimal1 = ActiveCell - Round(ActiveCell, 0)
Ldecimal2 = (ActiveCell - Ldecimal1) * 60

Shours = Round(ActiveCell, 0) & ":" & Ldecimal2

End Function

View 5 Replies View Related

Function That Is The Equivalent Of WORKDAY But For Hours Instead

Mar 14, 2007

I am in need of some excel advice relating to date calculations. Basically I need a function that is the equivalent of WORKDAY but for hours instead. I have a series of events that take a certain length of time to complete, most of them less than a day but some more than. By way of example see the screenshot below:

In reality the last three operations would have to take place on the 27th of April, with the Welding operation starting on the end of the 25th around 7pm. The plant is running a 24 hour day, and works 5 days a week. How can I calculate the times in hours offset rather than going day by day? I need to account for

* Weekends
* Fixed Holidays
* Operations running as seamlessly as possible

Any advice welcome. I have attempted to use WORKDAY with the number of days to deduct rounded to the nearest day and then subtracting the operation time but this results in errors where operations would cumulatively go over a working day. The objective is by knowing when the end product is needed and knowing how long each operation takes it is possible to discover when to start manufacture. VBA or Formula code is fine as this will be integrated into a VBA project.

View 6 Replies View Related

Function: List Dates In Increments Of 6 Hours...!!

Sep 30, 2008

I'v been trying to do this for a while now. I need a function that takes this date (which is not a set format in Excel, so has to be 'general'):

10.10.2007 00:00
10.10.2007 06:00
10.10.2007 12:00
10.10.2007 18:00
11.11.2007 00:00
...
...
...etc

I need a function that increments this type of date format in a column.

View 5 Replies View Related

Counts The Number Of Hours That This Person Works & Lookup Function

Jan 25, 2008

I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings:

Cell A - Name
Cell B - Monday
Cell C - Tuesday
Cell D - Wednesday
etc

Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem.

The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.

View 5 Replies View Related

Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours

Nov 4, 2008

In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time.

Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.

View 9 Replies View Related

Calculate Hours Worked With Lunch / Overtime / Holiday / Vacation And Saturday Hours?

Jan 13, 2014

Lunch is not paid. Holiday and vacation hours get calculated at the regular pay rate. Overtime is anything in excess of 8 hours per day and/or in excess of 40 hours per week and/or over 5 working days per week. Saturdays for most the employees will be overtime because it will be their 6th workday of the week; but it will be regular time for one employee as it will only be his 5th workday of the week.

For accounting and payroll purposes, we need the totals to display in both hour and decimal format.

So far, I have Lunch, Regular and Overtime hours figured out, but I still need to work with Saturday, Vacation and Holiday hours. Also, currently, the time in and out has to be typed in with the colon and AM or PM. Is there another way to input the info without having to type in those items? I'm trying to make it as user friendly as possible.

View 2 Replies View Related

Difference Between Dates And Times In Days , Hours Mins ( Working Hours )

Mar 2, 2010

The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie :
Difference between
02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes

=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))

The following displays it in the Hrs and Mins format

=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))

View 9 Replies View Related

Adding Hours Worked Minus Lunch Break IF Over 5 Hours?

Dec 15, 2013

I'm attempting to make a simple time sheet for a handful of employees. I'd like to enter the clock in time and clock out time for each day. The end cell should be the running total for the week. The tricky part for me is having the formula subtract an hour for each day that is over 5 hours.

View 3 Replies View Related

Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only

Jan 29, 2010

Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....

View 9 Replies View Related

How To Calculate Hours Between 2 Or 3 Days Exclude Non-Working Hours

Dec 29, 2013

I'm having difficulty to calculate hours between 2 or 3 days exclude non working hours.

Attached is the example of start date with time & end date with time.

The situation is like "when the case log in till the case assist in working hours." so i will get the hours from case log to case assist.

Testing.xlsx‎

View 8 Replies View Related

Calculating Actual Hours Worked Only In Core Hours?

Aug 11, 2010

I have been working on a timesheet but the problem I have come across is calculating actual hours worked only in the core hours and any work outside the core hours is calculated in the outside hours column. A standard work day is 7.6 hours working between 8.30am and 5.00pm. However if someone was to commence work either before 6am or after 8pm this is outside of core hours. I have attached an example of my timesheet for you to see what I am talking about.

View 3 Replies View Related

Calculate Hours Between Date And Time Within Business Hours?

Apr 4, 2013

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?

08/03/2013 13:32:00
02/04/2013 09:32:50

View 4 Replies View Related

Looking For A Formula That Can Separate Day Shift Hours From Night Hours?

Aug 6, 2013

I have got a formula that can separate day shift hours from night hours, in this case night begins at 7pm to 7am, however the problem is after 12am we get into negative numbers, what formula would fix this and can be combined with the formulas below?

E10 = 19:00 or start of night time hours
B3 = start time
C3 = end time
D3 = day hours workeds =24*IF(E10

View 1 Replies View Related

Count Hours Between 2 Times Based On Hours In Another Cell

Jun 11, 2008

A1 is 10 (10 hrs worked) , A2 is 10:30am (in time), A3 is 9:00pm (out time), A4 needs to be the total hours and minutes between A2 and A3 based on the hours listed in A-1. What i need is a formula that will calculate the hours and minutes between the 2 times based on hours entered in A1 but that will also compensate for a manadatory 30 minute lunch that needs to be deducted from the total hours if hrs listed in A1 are more than 6.

example: worked 10HRS, 10:30am to 9:00pm, Total hrs is 10hrs 30min, which should be just 10 since the lunch is a none work time and must be subtracted.

If a person worked more than 6hrs, they must take a lunch. if they worked less, than 6 then they don't have to. I need a calcuation to recognize the greater than, less than factor into the equasion also.

View 9 Replies View Related

Time Scheduling: Take Out A 30 Min Break If The Hours Worked Is Over 6 Hours

Jan 13, 2009

I am making a schedule and I would like it to take out a 30 min break if the hours worked is over 6 hours.

I have so far

A B

1 11:00 7:30

=24*(B1-A1)

Gives me 8 hours, I would like it to subtract the 30 minutes only ifthe sum is over 6 and not alter the sum if it is under 6.

View 9 Replies View Related

Format Total Hours To Days, Hours & Minutes

Aug 30, 2007

1) The output of an excel duration is : 22.00:8.00:25.00 ( day:hour:minutes ) - excel cannot average and work with this number format

2) resolution - =(LEFT(L2,4))+MID(L2, FIND(":",L2)+1,4)/24+MID(L2, FIND(":",L2,7)+1,4)/1440 as an array and Custom Format the cell as [h]:mm - works perfectly.

Q: to be conistent, the initial reporting is dd:hh:mm and then I convert to hh:mm so that excel can process the data. How can I convert from hh:mm to dd:hh:mm so that the excel report can be consistent in presenting the data to senior management?

example attached.

View 9 Replies View Related

Convert Hours To Fractions Of Hours

May 28, 2009

I am attempting to convert a spreadsheet of times (listed in the format 06:15:39.62, where 06 is the hour, 15 is the minutes, 39 is the seconds, and .62 is in truncated miliseconds) into fractions of hours (so, 6.25 [NOT 6:25!]). I've so far been doing it manually for each value, which is quite tedious (doing basic division of seconds and minutes into hours, to find the fraction) but I'd like a single formula which I can then apply to the whole spreadsheet.

View 2 Replies View Related

Displaying Sums Of More Than 24 Hours, As Hours.

Feb 1, 2010

I am trying to compute a running total of hours (from row 1) in row 2 Example................

As you can see, when the sum exceeds 24 (moving to the right across row 2) the answer resets, so to speak. Cells are formatted as time. This format *seems* incapable of recognizing quantities of hours over 24 except as days, as it were. This is obviously useful in most sorts of cases but not in this sort of case.

If I simply want the aggregate number of hours expressed as such am I doomed to failure whenever the total exceeds 24? In reply to a somewhat similar enquiry elsewhere in this forum, advice was given to format a cell as Elapsed Time. I dont see such a choice in my dropdown menu.

View 2 Replies View Related

Convert Second To Hours And Mins (over 24 Hours)

Apr 21, 2009

i need to convert second to Hours and mins and can do so using:

View 3 Replies View Related

Time Conversion: Convert A Time From Hours/Minutes To Hours/Tenths

Oct 10, 2009

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?

View 3 Replies View Related

Convert Hours Worked In Week To Hours Worked In Month With Formula?

Feb 21, 2013

Is there a way to conver a persons time spent (given in weeks) to adjust/convert to show per month. Attached is the sheet. Do note that week 2/25 - 3/1 is a combination of Jan and Feb so hours should be logically divided into jan and feb...

Name 2/18 - 2/22 2/25 - 3/1 3/4 - 3/8 3/11 - 3/15 Feb mar
Tom 40 10 0 20 ?? ??
name
2/18-2/22
2/25 - 3/1
3/4 - 3/8
3/11-3/15
Feb
Mar

tom
40
10
0
20
??
??

View 3 Replies View Related

"Time" Function To Find Time&date - ?hours

Dec 17, 2008

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

View 4 Replies View Related

Calculating Hour Outside Core Hours When All Hours Fall Outside Core

Apr 22, 2014

I am trying to set up a time sheet for staff where it calculates hours worked in and outside of core hours. I can do a calculation to work out what they have worked in and out of core hours providing that part of their time starts of finishes in core times. The problem I am having is when some one works only outside core hours. Our core hours are 6am to 6pm. The problem is when they work from say 7pm to 1am. This is irregular work paid as overtime not shift work.

View 5 Replies View Related

Sum Of Working Hours

May 29, 2014

In attached file I need doing sum where total working hours are not showing correct.

TotalingTime.xlsx‎

View 14 Replies View Related

Sumproduct With Hours

Jan 19, 2014

I am trying to do a sumproduct formula with hours and it is not calculating right.

Here is my current formula:

=SUMPRODUCT(([Productivity.xlsx]Occupancy!$A$2:$A$5000>=$C$1)*([Productivity.xlsx]Occupancy!$A$2:$A$5000<=$C$2)*([Productivity.xlsx]Occupancy!$B$2:$B$50ry00=$A4)*([Productivity.xlsx]Occupancy!$C$2:$C$5000))

My values in Column C that I am trying to get the sum of are:

1:24:49
:59:08
:58:46
1:39:19
1:10:08
1:13:15
1:04:23
1:43:04
1:23:55
1:48:11
1:42:41

The result of the sumproduct I am currently getting is 7824:00:00, however when I select the above values, Excel is showing it should be 13:09:45 in the status bar, which seems accurate.

I am not sure what I am missing to get this same number.

View 6 Replies View Related

Count Hours

Sep 29, 2008

How to count the hours using Excel cells (for time table scheduling)....for example : 11:00-9:00 is 10 hours, "OFF" is 0 hour, "X" means 0 also, 10:00-7:00 is 9 hours, and so on. But when I try to put the hours, they show ###### in the cells? I know the time table is mixed with numbers and letters, that's why I'm confused to use any formula to do it, but my boss told me NOT to change the original format what so ever.

View 14 Replies View Related

Totalling Hours From One Day To The Next

Oct 15, 2008

My company works 24/7 and with that some people on nights starting prior to midnight and then finishing after. Of course the "regular" formula works well in calculating between 00:00 onwards but I am getting an error when for instance the person starts at 20:00 and finishes at 04:00. I have tried numerous ways but still the error persists.

View 5 Replies View Related

Calculate The Hours

Jun 16, 2009

How to calculate the Hours
Check the attached file

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved