Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Not Calucating Right
Hi,
I have a work sheet with the following cells formated as Custom h:mm AM/PM with the data: D13 E13 F13 G13 8:00 12:00 12:30 4:30 and in cell H13 has the following formula: =(E13<D13)+E13-D13+(G13<F13)+G13-F13 The problem is cell H13 total is 20:00 hours but should be 8:00 hours. I have two questions: 1. How can the above formula be changed in cell H13 to total 8 hours. 2. What needs to be done so that cell H13 data looks like this 8 or 8.5 instead of 8:00 or 8:30? Thank you for your help, jfcby |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Not Calucating Right
1) Your 4:30 is 4:30 AM not 4:30 PM
2) Multiply the answer by 24 and format as General -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "jfcby" wrote in message ... Hi, I have a work sheet with the following cells formated as Custom h:mm AM/PM with the data: D13 E13 F13 G13 8:00 12:00 12:30 4:30 and in cell H13 has the following formula: =(E13<D13)+E13-D13+(G13<F13)+G13-F13 The problem is cell H13 total is 20:00 hours but should be 8:00 hours. I have two questions: 1. How can the above formula be changed in cell H13 to total 8 hours. 2. What needs to be done so that cell H13 data looks like this 8 or 8.5 instead of 8:00 or 8:30? Thank you for your help, jfcby |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Not Calucating Right
Your data in G13 is registering as 4:30 *AM*.
As you described your custom format, the AM *should* be visible in the cell! If you don't want to enter your times as 16:30 Then enter them as 4:30<spacep which will make XL read the correct, intended input. For your second question, revise your formula to: =((E13<D13)+E13-D13+(G13<F13)+G13-F13)*24 And format H13 to General or Number. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "jfcby" wrote in message ... Hi, I have a work sheet with the following cells formated as Custom h:mm AM/PM with the data: D13 E13 F13 G13 8:00 12:00 12:30 4:30 and in cell H13 has the following formula: =(E13<D13)+E13-D13+(G13<F13)+G13-F13 The problem is cell H13 total is 20:00 hours but should be 8:00 hours. I have two questions: 1. How can the above formula be changed in cell H13 to total 8 hours. 2. What needs to be done so that cell H13 data looks like this 8 or 8.5 instead of 8:00 or 8:30? Thank you for your help, jfcby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|