Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Military time not properly calulated
I have two cells that are set for h:mm and the time will switch throughout a
24 hour time period. For some reason ever fix I have tried, the time is still not calculated properly. For example: The first cell is 10:18PM [h:mm] and the second 2:38AM [h:mm] and the resultant cell has the formula (currently) as: =C42-E42+(C42<=E42)*2400 I have also tried =MAX(E42,C42)-MIN(C42:E42) Both formulas return a value of 19:40. The difference between 10:18PM to 2:38AM the following day shows up as 19:40. Shouldn't the result be like 4 hours and 20 minutes. Or at least that is what I am trying to attempt the output to be. Does anyone have the answer? tia... Shawn |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Military time not properly calulated
=(C42-E42+(E42C42))*24
Format as general "0be1" wrote: I have two cells that are set for h:mm and the time will switch throughout a 24 hour time period. For some reason ever fix I have tried, the time is still not calculated properly. For example: The first cell is 10:18PM [h:mm] and the second 2:38AM [h:mm] and the resultant cell has the formula (currently) as: =C42-E42+(C42<=E42)*2400 I have also tried =MAX(E42,C42)-MIN(C42:E42) Both formulas return a value of 19:40. The difference between 10:18PM to 2:38AM the following day shows up as 19:40. Shouldn't the result be like 4 hours and 20 minutes. Or at least that is what I am trying to attempt the output to be. Does anyone have the answer? tia... Shawn |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Military time not properly calulated
Theetless mama;
Thanks for the reply. I have tried this formatting only my output cell and I get: 19.66666667 as a result. Did I not follow your directions properly or something? Shawn "Teethless mama" wrote: =(C42-E42+(E42C42))*24 Format as general "0be1" wrote: I have two cells that are set for h:mm and the time will switch throughout a 24 hour time period. For some reason ever fix I have tried, the time is still not calculated properly. For example: The first cell is 10:18PM [h:mm] and the second 2:38AM [h:mm] and the resultant cell has the formula (currently) as: =C42-E42+(C42<=E42)*2400 I have also tried =MAX(E42,C42)-MIN(C42:E42) Both formulas return a value of 19:40. The difference between 10:18PM to 2:38AM the following day shows up as 19:40. Shouldn't the result be like 4 hours and 20 minutes. Or at least that is what I am trying to attempt the output to be. Does anyone have the answer? tia... Shawn |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Military time not properly calulated
Let's say your
C42 =20:00 E42 =3:00 (3 am the next day) =(E42-C42+(C42E42))*24 the result will be 7 hours "0be1" wrote: I have two cells that are set for h:mm and the time will switch throughout a 24 hour time period. For some reason ever fix I have tried, the time is still not calculated properly. For example: The first cell is 10:18PM [h:mm] and the second 2:38AM [h:mm] and the resultant cell has the formula (currently) as: =C42-E42+(C42<=E42)*2400 I have also tried =MAX(E42,C42)-MIN(C42:E42) Both formulas return a value of 19:40. The difference between 10:18PM to 2:38AM the following day shows up as 19:40. Shouldn't the result be like 4 hours and 20 minutes. Or at least that is what I am trying to attempt the output to be. Does anyone have the answer? tia... Shawn |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Military time not properly calulated
You've got your formula back to front. You need to subtract start time from
finish time, not vice versa. If 10:18 PM is your start time in C42, and 2:38 AM is your finish time in E42, then your formula is =E42-C42+(C42E42) formatted as time, giving 4:20 or =(E42-C42+(C42E42))*24 formatted as general if you want to convert to hours. -- David Biddulph "0be1" wrote in message ... Theetless mama; Thanks for the reply. I have tried this formatting only my output cell and I get: 19.66666667 as a result. Did I not follow your directions properly or something? Shawn "Teethless mama" wrote: =(C42-E42+(E42C42))*24 Format as general "0be1" wrote: I have two cells that are set for h:mm and the time will switch throughout a 24 hour time period. For some reason ever fix I have tried, the time is still not calculated properly. For example: The first cell is 10:18PM [h:mm] and the second 2:38AM [h:mm] and the resultant cell has the formula (currently) as: =C42-E42+(C42<=E42)*2400 I have also tried =MAX(E42,C42)-MIN(C42:E42) Both formulas return a value of 19:40. The difference between 10:18PM to 2:38AM the following day shows up as 19:40. Shouldn't the result be like 4 hours and 20 minutes. Or at least that is what I am trying to attempt the output to be. Does anyone have the answer? tia... Shawn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to I convert standard time to Military or 24 hour format? | Excel Discussion (Misc queries) | |||
Military time trouble | Excel Worksheet Functions | |||
Using military time but with 100 minutes not 60 minutes | Excel Discussion (Misc queries) | |||
in excel totaling weekly hours military time | Excel Worksheet Functions | |||
How do I convert Military Time to minutes? | Excel Worksheet Functions |