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

 

[Yongle]

You must have a 2nd date somewhere or another indicator otherwise how do you know the which day the 2nd value refers to?
How does Excel know that it is 3pm on the 2nd as opposed to 3pm on the 25th?

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Day &
time 1[/td][td]Day &
time 2[/td][td]Hours[/td][td] Formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

01/01/2019 12:00​

[/td][td]

01/02/2019 15:00​

[/td][td]

27: 00​

[/td][td] =B2-A2[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Day1[/td][td]Time1[/td][td]Day2[/td][td]Time2[/td][td]Hours[/td][td] Formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

01/01/2019​

[/td][td]

12:00:00​

[/td][td]

02/01/2019​

[/td][td]

15:00:00​

[/td][td]

27: 00​

[/td][td] =(C2+D2)-(A2+B2)[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

 

[Yongle]

Using an indicator

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Day1[/td][td]Time1[/td][td]Time2[/td][td]Next
Day[/td][td]Hours[/td][td] Formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

01/01/2019​

[/td][td]

12:00:00​

[/td][td]

15:00:00​

[/td][td]Y[/td][td]

27: 00​

[/td][td] =IF(D2="Y",(A2+1+C2)-(A2+B2),C2-B2)[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

 

[PlumGr8]

Yea, after i posted it and i was thinking about it, i was realizing there was no way to do it without the 2nd date option. I am limited in what i can do though, the 3rd option you gave could work. Other option i was thinking, is there a day to do "day 1, day 2, time 1, time 2, hrs?

Thanks for the replies. And those formulas seem to be much simpler then the initial ones we had.

 

[Yongle]

Is this what you mean?
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td] Date[/td][td] Start[/td][td] Day1[/td][td] Day2[/td][td] Hours[/td][td] Formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

01/01/2019​

[/td][td]

12:00:00​

[/td][td]

15:00:00​

[/td][td][/td][td]

3: 00​

[/td][td] =IF(ISBLANK(D2),C2,D2+1)-B2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

01/01/2019​

[/td][td]

12:00:00​

[/td][td][/td][td]

15:00:00​

[/td][td]

27: 00​

[/td][td] =IF(ISBLANK(D3),C3,D3+1)-B3[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet5[/td][/tr][/table]

 

[PlumGr8]

Not exactly. But they are al giving me ideas. I just have to find the simplest "dummie proof" least intrusive way. Going to experiment with 2 options. I am leaning towards the one you did that says "Y" for another day. Just wondering if there is a way i can make it a clickable value and still work it into the formula?

Thanks for all the helps also, you've been awesome

 

[PlumGr8]

So i experimented with 2 of your examples, both "work" but not getting the correct values? going from 12:00 to 15:00 instead of 3 i get .1? And if i go over 24 hrs i get "1.1"?

There are also some underlying VB rules from when this was setup years ago, not sure if that is conflicting?

 

[Yongle]

You have a short memory my friend

You began the thread with:
Ok, so not the simple "change to [H]:MM" solution

- the number format format for that column needs to be:

[h]: mm

 

[PlumGr8]

You have a short memory my friend



- the number format format for that column needs to be:

[h]: mm

haha touche, i thought i tried that, but it wasn't changing correctly. I will try that again and see how it goes haha.

 

[PlumGr8]

Ok, so success with doing that. Which now leads me to a couple other things to sort out. So now i get say 26.45 hrs. I changed [h]:mm to [h].mm, but is there way to get it to round to only one decimal? to go from 26.45 to 26.4? Lots of other workbooks etc pull data from this page and to change any of it i need all values to match, so really need to get it to go to 26.4. Or if i need a slightly different formula?

I did end up using the 2nd suggestion and that may be the way i go, if i can get this last thing to work.

I also did try the version with the "y" value, and that also worked. Same thing with the end result needing to be 26.4, and can i make a simple drop down saying y or n, or a check box that will work.

I have to keep this all as simple as possible not for me, but for all the other people that follow through here.