If AND Formula Using Hours And Minutes  Query
I am trying to write a formula that will take the value of cell J9 which is in hours and minutes format (hh:mm) and return a value in £.
ie. if J9 5hours and 12hours £15.00
I used this
=IF((J9)5,(J9)24,(J9)>24),15,))) returns #Value!
It won't work as it won't accept the hours and minutes format in J9. If I maually enter a value in J9 like 7.00 it returns the correct £12.00
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
 Converting Minutes Into Hours And Minutes Using A Formula
 Formula To Total Hours And Minutes In A Column
 Formula To Deal With Time, Hours & Minutes
 Convert 3786 Minutes To Day:hours:minutes
 Convert Hours To Minutes. Display Total Minutes
 Formula To Comput Hours:minutes Between Time On Different Dates A Couple Of Days Apart
 Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only
 Convert Decimal Hours And Minutes To Minutes
 Convert Hours & Minutes To Minutes `
 Format Total Hours To Days, Hours & Minutes
 Time Conversion: Convert A Time From Hours/Minutes To Hours/Tenths
 From Decimal To Hours And Minutes
 Add Up All The Hours And Minutes
 Express Minutes To Hours
 Adding Hours: Minutes
 Converting Hours Into Minutes
 Convert Hours To Minutes
 Adding Minutes To Hours
 Adding Time :: By Hours And Minutes
 Convert Minutes Into Decimal Hours
 Days, Hours, Minutes And Seconds
 Convert Hours To Minutes And Seconds
 Calculate # Of Hours & Minutes
 Amending Data To Hours And Minutes
 Add Together Different Cells With Hours And Minutes
 Calculating Times: Minutes And Hours
 Adding Seconds And Minutes To Get Hours And Days
 Convert Number To Hours & Minutes
 Calculating Difference In Times As Hours And Minutes
 Format To Calculate Total Hours And Minutes
 Calculate Hours And Minutes Between Two Dates/times..
 Expressing A Number Into Dayshoursminutes
 Calculate Hours: Sum Up The Minutes In The Particular Column??
 Time Format Why I Enter Colon Between The Hours And Minutes
 Time Card & Sum Hours And Minutes
 Calculate Time Differrence In Days Hours Minutes
 Cell Formated To Show Days, Hours, Minutes, Seconds
 Subtract Date/time And Get Elapses Days, Hours, Minutes, Etc
 Break Time Difference Into Minutes & Military Hours
 Format Time To Show Hours, Minutes & Seconds
 Convert Total Time To Days & Hours, Minutes
 Subtract Date & Time To Show Total Hours & Minutes
 Convert Decimal Minutes & Seconds To Days & Hours
 Convert Seconds Into Hours, Minutes & Seconds
 Convert Hours, Minutes And Seconds Into Total Seconds
 Hours:minutes:seconds Convert To Seconds
 Convert Seconds Into Hours:Minutes:Seconds
 Need Formula To Convert Minutes Into Hh:mm:ss
 Array Formula: How Many Minutes Of Tickets Are Open Within A Particular Month
 Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours
Converting Minutes Into Hours And Minutes Using A Formula
creating a formula for converting time data that has been created in an excel spreadsheet in minutes i.e. 516 minutes which I need to turn into Hours and Minutes i.e. 08:36 I am not experienced using Formulas, apologies if this question has been posted before, I did use the search facility to look for threads, but could not find anything related
View Replies!
View Related
Formula To Total Hours And Minutes In A Column
I am using a formula such as =Text(A5E5,"H:MM) to get the difference in clockin time and clockout time on a daily basis (MondaySaturday). I want to add the results as a total for the week. I am not sure what formula to use to get that result. I prefer not to use decimals unless I have to. Also, the above formula does not work when the time goes past 12 midnight.
View Replies!
View Related
Formula To Deal With Time, Hours & Minutes
I've got a worksheet that deals with dates and time. Similar to a checkbook, I need to take the balance and subtract what's taken from that. But the writer copied this from another software program and it's in this format. How do I convert it to a time format? Or is that what I really need? Right now the taken and balance are both in a general format. I just need to know on any of the given days what the balance of this person's time was in hours and minutes.
View Replies!
View Related
Convert 3786 Minutes To Day:hours:minutes
I'm trying to convert 3786 minutes to day:hours:minutes. So divided it by 1440 which is 2.63... but I want this displayed in the worksheet as 2 days 1 hour and 3 minutes (02:01:03), I just can't seem to get it to work and it seems quite simple... but I'm missing something.... I was trying a custom format like dd:hh:mm or [d]:hh:mm and I was also trying a convert function and =day/1440+hour +minute
View Replies!
View Related
Format Total Hours To Days, Hours & Minutes
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 Replies!
View Related
From Decimal To Hours And Minutes
I have a spreadsheet that keeps track of my flight time by the day and then totals it for the month however, I keep it in a decimal number (i.e. 89.23). I want the final total number to be in Hours and Minutes hhh:mm and can not for the life of me figure out the formula to make it come out in hours and minutes. I can either convert the one final decimal number over to hours and minutes or add the entire collumn of decimal numbers and have THAT total converted to hours and minutes. It's doesn't matter to me. I just can't figure out the formula.
View Replies!
View Related
Add Up All The Hours And Minutes
i am trying to make a employee work hour sheet so i can add the time and it add up all the hours and minutes he/she been working . now what i am trying to do is to enter 810 in the cell it automatically change it to 8:10 format but the problem it change it to 12:00:00. even when i enter 083612 it again change it to 12:00:00. now i have used the format cell > time and no luck. i already removed and installed my office but i still have the same problem.
View Replies!
View Related
Adding Hours: Minutes
Unsurprisingly I have a spreadsheet: Column D contains the total hours that a component has been installed for Column C contains the additional hours the component acquired I need the total of these 2 sets of time added together to give me the new hours that the component has acquired. All cells have been formatted [h]:mm:ss I attach an extract of the spreadsheet. At the moment the values in column D are all calculated manually. When I add the values together Excel returns #Value! The annoying thing is that in cells B2 and A3 I tested this and can do this with no problem...the value in B3 is the sum of B2 and A3.
View Replies!
View Related
Converting Hours Into Minutes
I know similar questions have been asked in the past, but I can't seem to get this to work for my specific case. I need to convert hours into minutes, and these times do not conform to a 24 hour clock. For example, I need to convert 1000:15 into 1000.25
View Replies!
View Related
Convert Hours To Minutes
I have a database which shows a time some one came into work and a time they left. Formatted as HH:MM. I can minus the time they left from the time they came in...to work out how many hours they worked. However, whenever i want to work out how many mintes it is (time left  time in * 60). It does not seem to work. Example A1  Time In 09:00 A2  Time Out 17:00 A3  Hours = 8 (A2A1) A4  Mintes should be 480. (A3*60)
View Replies!
View Related
Adding Minutes To Hours
i don't know why this one stumps me but it does. I have a production schedule which shows how many hours and minutes are remaining for a given production run, i.e. 6.4 or 3.2. i want to do two things with these values. 1) add the 6.4 to the current time, something like a1 + now(0) to get the time the production run should end. and 2) i want to add the number of minutes from a calculation to the current time, ie. 10:14 AM + 122 minutes = actual time the production ends. Nothing i do seems to give a desired result  including formatting the cell to any variation of the time format  including custom.
View Replies!
View Related
Amending Data To Hours And Minutes
I am using Excel 2003 and I have two columns of data, one column shows how much work for a particular worktype should be done in an hour and the second column shows what I’m expected to get in over the next month. I have added a third column to show how long it would take to complete the expected work. What I would like that third column to show the data in hours and minutes rather than a percentage of 100, so on a linked TAB on the main sheet it will show clearly as a time so staff hours can be allocate to a worktype. Is this simple to do. I have attached an example workbook.
View Replies!
View Related
Calculating Times: Minutes And Hours
i need to get a formula that will calucate hours and min. its for how many hours the employee has not worked. some of them would be strait hours some would be just min there is no way to tell. example lates 2 hours anp(absent no pay) 12 hours sicks 55.5 hours no calls early outs 21 min (this is just an example if it were real this person would be fired) i know this adds up to 69.85 hours but i can't fuiger out a way to get it to calucate in excel. i know i could have it all changed to min and then devied by 60 to get the hours but how do i get it to read what is mins and whats hours?
View Replies!
View Related
Calculating Difference In Times As Hours And Minutes
I need to calculate the difference between a start time and end time in hours and minutes. Start 01/07/2008 11:40 End 01/08/2008 19:28 Start and End columns are formatted as 'Custom' m/d/yyyy h:mm. I'm not sure what formula to write to calculate the hours and minutes between the two times. Everything I've tried doesn't count over 24 hours. Also what do I format the result cell as?
View Replies!
View Related
Format To Calculate Total Hours And Minutes
Having trouble adding a column of minutes and converting the total into hours and minutes. Say Cell A1 through Cell A18 each have 12 minutes in each cell. I want cell A19 to tell me how many hours and minutes of total time that have elapsed. I have tried hh:mm, [hh]:mm, but nothing works.
View Replies!
View Related
Expressing A Number Into Dayshoursminutes
I'm hoping someone here might be able to help me please? I am trying to write a function that will convert a number into dayshoursminutes. I have managed to get as far as hoursminutes using the following function. =INT(A1/60)&"h "&ROUND(MOD(A1/60,1)*60,0)&"m" e.g. If A1 = "7090" the result of this function will be "118h 10m". I now need to express the result as xxxd xxxh xxxm and this is where I am stuck!
View Replies!
View Related
Time Card & Sum Hours And Minutes
Just basic enter a start time in column A a finish time in column B and column C gives you total in hours and minutes and then maybe a way to sum those hours and minutes. how to do "Math" on time and how it should be entered (formatted) for it to work properly.
View Replies!
View Related
Subtract Date/time And Get Elapses Days, Hours, Minutes, Etc
I want a forumal to do in excel. Assuming that a workday is from 8:30am to 5:00pm. (also need to not calcualte Saturday and Sunday but this example doesn't show it). Wednesday, 7/18/07 at 4:00pm (minus) Friday, 7/20/07 at 9:00am (should equal) 10 hours or 1 day, 1 hour and 30 minutes. Basically what I want to do it determine how much work time has elapsed from the time a message was left on voicemail to the time that someone picks that message up. We only want to assume that a message can be retrieved duruing business hours but a message can be left at anytime!
View Replies!
View Related
Convert Total Time To Days & Hours, Minutes
I have a column of tasks that take a certain amount of time to complete formated as h:mm:ss. I want to total the column and convert the total to days, hours and minutes. Is that posible and if so how do I configure a formula and format the cell? example: task 1 54:00:00 task 2 20:45:00 task 3 27:05:20 task 4 51:10:45 total 153:01:05 How many days, hours and minutes?
View Replies!
View Related
Convert Decimal Minutes & Seconds To Days & Hours
I have two columns A and B. Column A contains problem title and column B is duration minutes. In column B we enter always min and seconds it took us to resolve certain issues (22.15 = 22 minutes and 15 seconds) . At the end of the month I need to run a report for my manager to show her by problem title how long it took my group to resolve issues and it should output as Hours/min/days.
View Replies!
View Related
Convert Seconds Into Hours, Minutes & Seconds
I have a spreadsheet that imports data relating to phone call durations. The information imports as a total number of seconds taken. What I need to do is to change that number of seconds into hours, minutes and seconds. Changing the format of the cells doesn't work. I've tried everything I can think of, and run out of ideas. example: 23 needs changing to 00:00:23 96 needs changing to 00:01:36 268 to 00:04:28 9374 to 02:35:14
View Replies!
View Related
Hours:minutes:seconds Convert To Seconds
I am trying to convert a number of "hours" "minutes" and "seconds", to give me a result in seconds only, in order that I may then financially cost the amount of time spent on a task. (A time and motion costing exercise) Example: 1119:48:06 Represents 1119hours:48minutes:36seconds spent on a task. I can manually convert this to 4031286 seconds, but it just takes too much time !!!(sorry!) The data is extracted from a database which is unable to split the hours,minutes,seconds into seperate fields, which prevents me from using three cells in excel.
View Replies!
View Related
Array Formula: How Many Minutes Of Tickets Are Open Within A Particular Month
I am trying to write an array formula that will work out how many minutes of tickets are open within a particular month. I have a formula shown on the first table which calculates how many minutes a ticket is open for within a particular month, but this only works for an individual ticket, Basically I neet to populate the 2nd table with an array formula that will calculate the number of minutes within the month that numerous tickets are open, effectively a SUMPRODUCT but I can't get it to work. The right hand side of Table one doesn't exist in my main file, I am trying to populate the 2nd table using only the first four columns within Table 1. ******** ******************** ************************************************************************>Microsoft Excel  My God.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)boutF3G3H3F4G4H4F5G5H5F6G6H6F7G7H7F8G8H8F9G9H9F10G10H10F11G11H11F12G12H12F13G13H13F14G14H14F15G15H15F16G16H16=ABCDEFGH1 01/02/2008 Jan 08Feb 08Mar 082 3129313W00000220/01/2008 17:33:2629/02/2008 02:36:5456037 16,035.8540,001.154W00000127/01/2008 01:05:4620/02/2008 03:17:52200 41.13158.875W00000122/03/2008 09:06:1824/03/2008 08:33:57789 ..............................
View Replies!
View Related
Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours
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 Replies!
View Related
