# If AND Formula Using Hours And Minutes - Query

Jan 23, 2009
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 9 Replies
ADVERTISEMENT
Aug 22, 2006

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 5 Replies
View Related
Mar 1, 2012

I have a program that outputs a total amount of time that a service was provided. This time is reported in hours minutes [Example: 01.08 (hh.mm)]. I need a formula that will "round" to the closest .25 based on a 7 minute window on either side. Here how it would need to work....

0 - 7 Minutes = .00 or 1.00 - 1.07 = 1.00

8 - 22 Minutes = .25 or 1.08 - 1.22 = 1.25

23 - 37 Minutes = .50 or 1.23 - 1.37 = 1.50

28 - 52 Minutes = .75 or 1.28 - 1.52 = 1.75

53 - 7 Minutes = .00 or 1.53 - 2.07 = 2.00

View 4 Replies
View Related
Jun 23, 2008

I am using a formula such as =Text(A5-E5,"H:MM) to get the difference in clock-in time and clock-out time on a daily basis (Monday-Saturday). 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 9 Replies
View Related
Feb 1, 2010

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 2 Replies
View Related
Nov 21, 2013

I have a data set that expresses time in hours and minutes but it can only be exported like so:

7h10

11h03

Any function I could use to convert those numbers into 7.6 or 11.03?

View 3 Replies
View Related
May 25, 2011

I have a spread sheet with a colum showing average time to complete a task. This is currently shown as Days:Hours:Minutes:Seconds (4:19:33:19). I meed it to be shown purely as minutes, or at least as hours and minutes.

View 4 Replies
View Related
Dec 5, 2006

I have a formula which will calculate the number of hours and minutes between two military times. I would like it to calculate the total number of minutes instead of hours and minutes. I have uploaded a small example of what i have so far.

View 3 Replies
View Related
Oct 14, 2009

Im trying to figure out the formula to comput hours:minutes between time on different dates a couple of days apart.

B12 = Date

B15 = Time (on date in B12)

Previous date and time are in AA9. I have been trying to us =SUM(B12+B15)-(AA9).

View 5 Replies
View Related
Jul 4, 2006

What formula will convert 4.50 to 530 minutes ( "Decimal Time" )

another example 16.50 to 1250 minutes.

View 13 Replies
View Related
Jan 21, 2009

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 9 Replies
View Related
Jan 29, 2010

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

View 9 Replies
View Related
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
Jul 4, 2007

I have a worksheet which I am trying to format as a template which includes inputting start times and end times of work and calculating how many minutes are taken to do the job. I just can seem to find the correct formula.

View 9 Replies
View Related
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
Dec 20, 2009

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 12 Replies
View Related
Oct 17, 2008

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 3 Replies
View Related
Jan 14, 2009

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 (A2-A1)

A4 - Mintes should be 480. (A3*60)

View 3 Replies
View Related
Sep 18, 2009

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 2 Replies
View Related
Nov 3, 2009

I Need to add together different cells with hours and minutes. The attached workbook explains.

View 3 Replies
View Related
Aug 31, 2009

Cell D2 (Receiving) : 8/17/2009 17:19

Cell E2 (Dispatch) : 8/18/2009 10:33

I need to know the number of hours and minutes between the two (E2 - D2) in Hr:mm format.

View 4 Replies
View Related
Jun 10, 2013

I am trying to calculate employees hours of work from information provided to myself.

Each employee is contracted to work 40 hours per week. The information provided to myself is in the format - Employee has worked 39.30 This is in fact 39 hrs 30 minutes. Obviously when I subtract 39.30 from 40.00 I get 0.7 and not 30 minutes. I tried formatting the cell containing these figures to hh:mm but it comes out at 15.30 & 15.00 respectively because it is over 24 hrs. How do I convert the digital number (0.7) to minutes I.e. 30 minutes.

I have tried to convert the 40 hrs into minutes but because the hours are greater than 24 then I struggle.

View 9 Replies
View Related
Oct 20, 2007

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 9 Replies
View Related
May 12, 2009

=SUM(B10:G10)....answer is 245 minutes

How do I express this in hrs and minutes, 4hrs 5 mins.

Then I need to * this by an hourly rate to get a salary figure.

View 9 Replies
View Related
Jun 12, 2006

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 2 Replies
View Related
Feb 10, 2014

Cell A1 contains 2, B1=220077:23

In C1 I created a function = (2*1000)/B1, I get error. I want the answer 2000/220077h23m = 0.009087713

View 5 Replies
View Related
Jan 8, 2007

I am trying to use the Mod function to convert the following:

I have time in hours in A1 as 12.66 and I need it to convert to 12 hours in A2, 39 minutes in A3, and 36 seconds in A4. What formula can I use to achieve this?

View 10 Replies
View Related
Oct 19, 2008

I want to Sum up the minutes in the particular Column, so that at the end with i put formala, the output would be X Hrs X Mins, one more thing i would like to do in it is , I want to copy paste the value of formula in next column, for eg the formula is in A6, i want value of that formula in B6.

View 4 Replies
View Related
Mar 10, 2009

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 5 Replies
View Related
Jun 6, 2014

[URL].....and the answer worked really well; up until a point of

Total Seconds32767

Time From Seconds09 hrs 06 minutes(s) and 07 second(s)

any higher than this value brings a #value error.

View 2 Replies
View Related
Sep 23, 2008

Does any know how I can convert 29.18:20:18 to total minutes?

View 7 Replies
View Related
Jun 4, 2006

If i have a start time of 5:00 am and a finishing time of 4:25 pm, what function do i use to get the total time of 11 hours & 25 minutes?

View 10 Replies
View Related
Dec 10, 2008

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 4 Replies
View Related
Feb 27, 2012

If I want to make a formula for a time such as

I have this time like 4:30 hour and i want to get this time by minutes like 270 minutes.

View 1 Replies
View Related
Sep 25, 2006

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 7 Replies
View Related
Feb 12, 2007

How can I convert an ordinary number i.e. 3.45 to Hours & Minutes?

A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.

View 9 Replies
View Related
Aug 29, 2007

How to I convert minutes into the equivalent fractional hours?

View 9 Replies
View Related
Jul 2, 2009

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 days-hours-minutes. I have managed to get as far as hours-minutes 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 5 Replies
View Related
Aug 10, 2009

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 3 Replies
View Related
Oct 18, 2008

Im trying to calculate hours and minutes between two dates/times over more than 24hrs. I have:-

A1 is 18/10/08 13:14, B1 is 20/10/2008 12:20, C1 is 1:23:05

Using the formula C1 =INT(B1-A1)&TEXT(B1-A1,":hh:mm")

But what I really need is just Hours and Minutes so the above should read 47:05.

View 5 Replies
View Related