i have =TIME(LEFT(F17,FIND("h",F17)-1),RIGHT(F17,2),0). which gives a time in the format 9:30PM, or 10:30AM etc. How would i change the formula to give me a time minus 15 mins. Eg, if the above formula gave me 8:45PM, i would want it to show 8:30PM
View 4 Replies
If a person does a full day I want the formula to calculate how many hours minus 30 mins for lunch i.e. 7:30 - 4:00 = 8 hours. I have done this and it works fine, the problem comes when a person does a half day i.e. 7:30 - 11:30, this will read 4.5 hours. If this is the case I don't want the 30mins deducted so it should be 4.0 hours worked.
I have high lighted a column in the work sheet (yellow) whereby the user puts an 'x' in the appropriate cell if they are working a half day.
The probem is I cannot incorperate the two formulas in one cell (the Actual hours cell) I have the one formula in the actual hours cell and the other bolted on the end thus giving two different readings.
Formula to calculate time allotted minus time used and show the difference in hour and minute.
View 1 Replies View RelatedI have devised a simple formula when a member of staff enters a date and time into a cell it starts deducting from todays date and time. ie:
Cell A1 - Date and time is entered by staff
Cell B1 - Is the above cell +24hrs
Cell C1 - NOW()
Cell D1 - B1-C1 Formatted in hours ( [h] :mm:ss (Like a count down clock)
The problem i have is that i cannot get Cell D1 to show minus ours it just goes to negative and shows continous #####. Is there any way i can get Cell D1 to show the hours it has gone minus by.
I have a spreadsheet, but I need the following formula to make our lives easier here.
I have the following columns:
Rate, I/T/P/S, FT/PT
I need a formula that when I enter "I" for Infant it will give me the FT (full time) or PT (part time) rate. All the columns have drop down menus, so if I choose "I" under the ITPS column and then choose FT, I want the formula to be able to give me the full time rate which is 157.94
I am trying to round up or down in excel to the nearest 5 mins , but with no joy
ie 05:24 = 05:25
06:21 = 06:20
I have a workbook of which has different times in columns D and E. What im trying to do is where the time difference between the columns is LESS than 5 mins I need that row deleting and cells shifting up.
I.E lets say D14 is 05.24 and E14 is 05.50. I need that line keeping but if E14 was lets say 05.27 then I need that line deleting and cells shifting up.
I need to be able to do this right down the workbook until the last entry.
I am creating a very quick holiday excel sheet where people can fill in the hours they want on which days.
I have a summery sheet too.
So if someone wants to take a full day which is 7.4 hours. How would this be represented as Hours and minutes?
Formula that will change seconds to hours and mins, example 270 mins would show up as 4 hours 30 mins, not 4.5 hours. My second thing is how can I double the times in the same formula.
View 9 Replies View RelatedI have a time calculated and I want to know, Out of that time how much is above or below 8 hours.
Ex. 1: 7:30 - 8 = [result] "-:30" mins
Ex. 2: 11:00 - 8 = [result] "3:00" hours
OR
How can I convert 7:30 into 7.5 [decimal] and then subtract that from "8" to get the difference?
I know this is an easy one, I have done it many times before. It's crunch time and I am drawing a blank.
basically I am trying to make a time calculator for work, I want it to automatically add the hours up but then also minus break times and then give me a whole paid total hours worked that week.
CALCULATOR.xlsx
I have a data of complaints where I need to present it to the Management in such a way that the SLA period of 8 hours does not pass. Our office working hours are 7 AM till 7 PM. The complaint received should be escalated to concern section within 8 Hours of SLA time. I have the list of dates with received time. The complaint which could not be escalated today would be escalated next day. In this case is should deduct 12 Hours (7 PM to 7 AM, Non-working hours) from the time. How can I insert escalation date so as that it would deduct non working hours from it.
View 5 Replies View RelatedI have a requirement of calculating time spent on ticket, basically a difference between the time a ticket was logged and when it was resolved.
First, working days are Saturday through Wednesday, i.e weekends are Thursday & Friday.
Second, there are different resolver groups or teams which work on a ticket. These teams have different working hours, some work from 07:30 to 15:30 on weekdays, and others work 24/7. Each ticket will be assigned only to one resolver group.I also need to consider holidays.
So, my requirement is to check the resolver group for each ticket, and decide the working hours based on it. After this check has to be done for holidays, and then difference between logged time and resolved time needs to caluculated in minutes.
Tickets can be logged at any time of the day / week, it may not always be logged during working hours only. in such cases calculate should consider next business day / hour as the start time.
Raw data for tickets is available in =Sheet1!$A$1:$E$21
Ticket NoResolver GroupLogged DateResolved DateTime Spent123456
Team A5/21/2012 1:56:28 PM5/29/2012 2:10:53 PM343543Team B5/21/2012 9:31:02 PM5/27/2012 3:41:22 PM853732
Team C5/22/2012 2:28:06 PM5/26/2012 2:34:31 PM
[Code].....
=C4+(0.3*(C5-((C2-1)*C3)))
Where C4 is 'Qualifying time', C5 is 'Starting fuel', C2 is 'Lap number' and C3 is 'Fuel consumption'. I also plan to make the 0.3 a changeable variable.
I'm not fussed as to whether or not this is a realistic function that would work in a real race, it's only hypothetical. However I would like to have a function that would give me the over all race time at any given lap, not just the lap time for it.
I Have 20:00-04:00 as text in a cell, what i need is to take the 04:00 and show only if its after 00:00 and before 07:00 and show it as time value. This is where I'm up to so far but i don't know how to ask it for > 00:00 and < 07:00.
View 4 Replies View Relatedcolumns consist of 1,2
I selected only 1 value of cells
I want to add comments to all the 1 value cells by selecting all 1 by shift + ctl +down
I can put same comments to all the cells?
If you want to have the same comment applied on many cells you could apply it to a single cell, then use the copy and paste special tool bar commands (under Edit) In Paste Special select "Comments" but value is going of that cell.
I have set up a spreadsheet to calculate time as followed:
A1 - Start Time
B1 - Finish Time
A2 - Hours Worked:
=IF(B1<A1,B1+1,B1)-A1
C1 - Start Time
B1 - Finish Time
C2 - Hours Worked:
=IF(D1<C1,D1+1,D1)-C1
and so on.........
I wanted to convert a variant to an integer. The variant data is stored in an array, the variant data was entered from a userform.
Private Sub CB_OilPrice_Click()
Dim xx As Long
UserFormSpecifyDetail.Show 'Enter data
For i = 1 To 120
xx = VarType(UserFormInputArray(i)) 'Returns 8
xx = Int(UserFormInputArray(i)) 'Returns error
Next i
End Sub
The value in UserFormInputArray = "50000"
error = Run-Time error 6, overflow
Excel 2003
I need to write a formula to minus a percentage of a total.
for example I want to take away 20% of the total of £8.75.
I have attached here an excel sheet with some data. I need to show the minus value in D5 as a plus sign, is there any conditional formula to work this out??
View 4 Replies View RelatedI have a formula when C15 is empty it shows -18 value. I need if the value is blank then show blank?
Code:
=IF((Data!C15-32)*5/9="","",(Data!C15-32)*5/9)
In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this
A1 = 23 days or any number days i needed
Cell range B1:C52
every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD
In B7 i have 10/09/2009 19:30:00. In C7 i have 11/09/2009 21:30:00. What i want is C7-B7 and give the number of hours in between which should be 26 hours. I've used this:
View 5 Replies View RelatedThe following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.
The answer I get is 308669
=(((F22*71449)*0.4)+16147/C22)
Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8
I have 6 rows (A-F) with dollar vallues. I am trying to create a formula on row G that will give me the highest dollar value out of rows A-F.
I’m looking for a formula that will give me a figure against the name in column A (name) while looking picking out a specific word within the column B, say "alarm"
So it reads
Alan Ingram 1
David walker 3
And so on...
I am trying to do something for a baseball game I am creating. What I am looking for is a formula for one cell that will basically give me a whole number when a certain criteria is met. I am also looking for this cell's formula to get part of it's formula from another cell in the document. So let's use cell A1 has 1.50 in it.
I want this other cells formula to be able to give me a 9 if A1 is 0.00-1.50, a 8 if A1 is 1.51-2.79, 7 if A1 is 2.80-4.30, a 6 if A1 is 4.31-5.49 and 5 if A1 is 5.50-500.00.
Is there a lookup formula that will give me multiple items? so in my example attachment i want to look up risk from section A to give me the item in section B, but for risk 7 for example it has multiple items (a,b,c), how would i get all them? even better would be if its possible to have in seperate cells
View 4 Replies View RelatedI'm trying to write a formula that searches for the name of the column on one sheet, finds that column on another, then results in the column letter. So, ideally, if the first three columns in the first tab were "Team," "Name," and "Total," respectively, I just want a formula to search for "Name" and give me back "B."
I want to do this in case anybody ever moves the "Name" column, for example, from column B to column C -- so then it would search for "Name" again and change the range in the formula to "C" because that's where that data lives now.
Currently, I'm using it for a COUNTIF() function that is a very simple: =countif(indirect("'"&$A2&"'!K:K"),$B2), where A2 is the sheet name. The latter half of $B2 is fine -- but I would love to have a more complicated formula take the place of the range "B:B" in case the "Name" column ever got moved.
I know that I an probably asking for too much but I must get the formula some way. OK, I need a formula for this:
lets say I have 3 columns (A,B,C (search criteria). In each Column there is a DIFFERENT number. There is 3 more columns (D,E,F (used to compare).Each column has 3 Different numbers. If 3 numbers match between the two sets of 3 numbers I want Column G to display "CCC". IF 2 numbers match I want G to display "CCH". If 1 number is a match I want G to display "HHC". If none of the numbers match I want G to display "HHH".
I can manually do these but it will take waaaay toooooooooo much time to do. If you can do this for me it would be a great help indeed because I'm trying to do other states. With this formula I can use Automate to do a large quantity of numbers.
