Help when counting OVER 24hrs

[PlumGr8]

Ok, so not the simple "change to [H]:MM" solution. I have a little different of a situation. I have to account for more then 24hrs at a time.

Example, i have a date of say 1/1/2019 with a start time of 1200 with and end time of of 1/2/2019 at 1500. Which would be 27 hrs total time.

But, we only go by the first date entry we don't have a way to do a 2nd date entry. Our current formula is

=IF(D2>C2,((D2-C2))*24,((D2+12-C2)-INT(D2+12-C2))*24

I thought maybe the 24 was the limiting value so i just tried to change that and no go. So, if there is an easy fix, or an easier formula that would do the job as well that would be awesome. Thanks for all the help

 

[PlumGr8]

So thought i was all set, but now a different situation. If the time start at say 0500 on the 1st and ends on the 2nd at 0200, it will show a negative number unless i hit next day. Also if i have a time that start at say 2300 on the 1st and end at 0100 on the 3rd, i can't get it to 26 hrs.

So,i think i am back to trying the date forumla? Doing that one, it should count properly knowing the the start and end date correct?

 

[PlumGr8]

So after all the info and experimenting, i have settled on using your 2 date formula. Given all the little subtle changes in drop downs etc i think that will be the best way now. i also did the *24 and seem to have it all going well.

One last question haha. As i type the formula as we do it in stages, meaning date/time then the other date/time several hrs later, i get random unfinished values in the total hrs column. Is there way for it to ignore values until the formula is completed? Or is that asking to much of excel?

 

[PlumGr8]

So in the end this is what i used from all your suggestions,

=(D2+E2)*24-(A2+C2)*24

and all is working well.

But, as i enter in data as i do day and time 1, hours will show deep negative #'s until i enter the other date and time. This also causes a problem as i have a AVG page that is getting thrown off by those deep negative numbers. Is there a modification to the formula to tell it to say leave blank if value less then 0? Or does this fall under conditional formatting? I played with a formula for "leave blank if" but i am not good enough to figure out the proper order.

Any other suggestion? Last step and so close haha

 

[Yongle]

How about something like this?
=IF ( D2 = 0 , "" , (D2+E2)*24-(A2+C2)*24 )

 

[PlumGr8]

Yesssssssssssssssssssssssssssssssssssssss haha. Freaking perfect Thank you tremendously for all the help. If you ever come to Vegas and i am home let me know i owe you a drink haha