When I enter the Time Started as 10:30 PM in cell A1 and 6:00 AM in cell B1, then I enter the equation (A1-B1) the answer in cell C1 is 0.75? Why? I want the answer to show 6hrs and 30 minutes worked. (hrs worked)
When I enter the equation =Text(A1-B1,"hh:mm") the answer in Cell C1 is 18:00. Why? I waht the answer to show 6hrs and 30 minutes or 6:30.
2006-12-06 08:20:02 · 4 answers · asked by V-Tel 1 in Computers & Internet ➔ Software
Enter the following formula in C3: =IF(A1>B1,B1+1-A1,B1-A1)
The above formula works like this: If A1 cell value is greater than the B1 value, we have a midnight between -so we must instruct excel that the B1 value is next day by adding 1 to its value,oherwise we do a normal substract. For your values (10:30 PM- 6:00 AM), the result of the formula is 0.3125(days) . To show the answer like 'hours:minutes' ,format the C1 cell as 'time', with "13:30" format type
0.3125 (days) * 24(hours in a day)=7.5 hours =7 hours and 30
mins
2006-12-06 10:30:36 · answer #1 · answered by Mihai L 2 · 0⤊ 0⤋
The problem is that it is assuming you are using the same day(not the next day, ie after midnight), so the time is calculated backwards. you need to put in dates to get the accurate answer. Since the number is negative time you will get #### as an answer so multiply the answer by -1, or reverse the order to read b1-a1. Also the answer is 7:30 not 6:30.
2006-12-06 08:29:55 · answer #2 · answered by mjboyle508 1 · 0⤊ 0⤋
It's showing 0.75 because the amount of hours worked is 7.5 hours, not 6.5 hours. You also have to enter the dates, since 10:30 PM is in one day and 6:30 AM is in another day.
2006-12-06 08:34:51 · answer #3 · answered by jim 6 · 0⤊ 0⤋
A B C a million 10:00 PM 8:00 AM 2 before each and every thing opt for the region in A and B and precise click and format it to time interior the format you want. click c and write in formula textbox =textual content(B1-C1,"hh:mm") and then press enter or perfect image it truly is done. remember to positioned "="signal remember in case you press delete in C after entering the formula it truly is going to be deleted.
2016-11-24 19:25:52 · answer #4 · answered by saleh 4 · 0⤊ 0⤋