Trying again for time in tenths of an hour.

[cblincoln43]

The numbers i use are in tenth of an hour, the time is for starting time (-) ending time (=) hours. or,
...f7.........e7...(=)...g7.
..5:30pm....2:00am.(=).8.5hrs.
I need:
...5.5pm... 2.0am .(=).8.5 hrs.
I have tryed formating cells to costom 0.00"hours"
in g7,,,=if(f7-e7<0,f7-e7+1,f7-e7)*24. and it returns #VALUE, in g7. I can't seem to get this problem past the midnight hour without creating a 36 hour day.
What am i doing wrong or can i get this thing to give me tenths for time?
This message was edited by cblincoln43 on 2002-04-07 05:32

 

[cblincoln43]

With you sir im very sure it will do what i need, only to try it is left. thank you again.

 

[cblincoln43]

ok its up and running. But i had to take the a for am and p for pm out of the pitcher. How can i get the input cells (start and end time) to take a & p and have no effect on the value? it gives #VALUE if entered now.

 

[Dave Patton]

The formula
=(F7+(F7<E7)-E7)*24

works correctly.

Ensure that you enter valid time in E7 and F7. Do NOT enter the times as TEXT. You can enter with for example
5:15 p and 2:00 a and Excel will show as
5:15 PM and 2:00 AM.

Ensure that you format the cell (where you record the formula) as general or as number.
This message was edited by Dave Patton on 2002-04-07 11:31
This message was edited by Dave Patton on 2002-04-07 11:33

 

[Dave Hawley]

Hi cblincoln43


Sorry, small typo in my reply, should be.

=IF(F7<0.5,F7*24,(F7-0.5)*24)

Where F7 contains your time. The formula could be shortened some-what, but would be at the expense of readability and have no advantage.

If you also want the AM and PM after the decimal use a Custom Format like:

[<0.5]General;0.0"AM";0.0"PM"


The other formula of Aladins Will return 17.5 for "5:30:00 PM", I assume you really want 5.5PM ?




_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-04-07 19:00
This message was edited by Dave Hawley on 2002-04-08 05:01