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]

In that case you do not want the answer to be TIME but a NUMBER
Convert time to a number by multiplying by 24
and format cell as a NUMBER to 2 decimal places

Like this:
=(CURRENT FORMULA)*24

so (using one formula as an example):
=IF(ISBLANK(D2),C2,D2+1)-B2
becomes:
=(IF(ISBLANK(D2),C2,D2+1)-B2)*24

and the irony of the story is that we have gone full circle and you were right after all

Ok, so not the simple "change to [H]:MM" solution

 

[PlumGr8]

In that case you do not want the answer to be TIME but a NUMBER
Convert time to a number by multiplying by 24
and format cell as a NUMBER to 2 decimal places

Like this:
=(CURRENT FORMULA)*24

so (using one formula as an example):
=IF(ISBLANK(D2),C2,D2+1)-B2
becomes:
=(IF(ISBLANK(D2),C2,D2+1)-B2)*24

and the irony of the story is that we have gone full circle and you were right after all

haha, well not entirely. We did get a formula that accounts for over 24 hrs traveled. But now that make sense where the *24 comes in. I will try that out tomorrow and see how it goes. Either way, your formula suggestion is still simpler then the one we currently have. Now just need to figure out the dropbox of clickable option

 

[Yongle]

Do not use checkbox - very cumbersome creating one for every row (although it could be automated with VBA)!

Alternative for you to try:

This allows user to click in column D (to change value to 1 or back to blank) and the formula in column E is very simple
In order to change the value user needs to be in a different cell before clicking

Right click on sheet tab \ click on View Code \ Paste code below into the code window \ {ALT}{F11} to go back to Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Cells.CountLarge > 1 Or .Row < 2 Then Exit Sub
        If Not Intersect(Columns("D"), .Cells) Is Nothing Then
            If .Value = 1 Then .ClearContents Else .Value = 1
        End If
    End With
End Sub

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]End[/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][/td][td]

3​

[/td][td] =(C2+D2-B2)*24[/td][/tr]

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

3​

[/td][td]

01/01/2019​

[/td][td]

12:00:00​

[/td][td]

15:00:00​

[/td][td]

1​

[/td][td]

27​

[/td][td] =(C3+D3-B3)*24[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet6[/td][/tr][/table]

 

[Yongle]

Or use a dropbox in column D with possible values 0 and 1
and formula in E2
=(C2+D2-B2)*24

or drop box with possible values 1 (for day1) and 2 (for day2)
and the formula adjusted accordingly
=(C2+D2-1-B2)*24

 

[PlumGr8]

Ok, so here is where i am at, trying to use the "*24" getting an error, here is my formula

=IF(E2="y", (A2+1+D2)-(A2+C2),D2-C2) the formula works, but if i wanna use the *24 i was getting issues.I tried it a couple ways and am getting hung up on this last step "for now" haha

 

[Yongle]

deleted by Yongle

 

[Yongle]

Your formula should work
- in all versions below column F values are formatted as numbers

YOUR FORMULA

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][th]

G​

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

1​

[/td][td] Date[/td][td][/td][td] Start[/td][td] End[/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][/td][td]

12:00:00​

[/td][td]

15:00:00​

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

27.00​

[/td][td] =(IF(E2="y", (A2+1+D2)-(A2+C2),D2-C2))*24[/td][/tr]

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

3​

[/td][td]

01/01/2019​

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

12:00:00​

[/td][td]

15:00:00​

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

3.00​

[/td][td] =(IF(E3="y", (A3+1+D3)-(A3+C3),D3-C3))*24[/td][/tr]

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

4​

[/td][td]

01/01/2019​

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

16:00:00​

[/td][td]

15:00:00​

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

23.00​

[/td][td] =(IF(E4="y", (A4+1+D4)-(A4+C4),D4-C4))*24[/td][/tr]

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

5​

[/td][td]

01/01/2019​

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

23:30:00​

[/td][td]

15:00:00​

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

15.50​

[/td][td] =(IF(E5="y", (A5+1+D5)-(A5+C5),D5-C5))*24[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet7[/td][/tr][/table]

CAN SIMPLIFY - by ADDING the result of IF (which is either 1 or 0) to the value in D2 before deducting C2

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][th]

G​

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

1​

[/td][td] Date[/td][td][/td][td] Start[/td][td] End[/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][/td][td]

12:00:00​

[/td][td]

15:00:00​

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

27.00​

[/td][td] =((IF(E2="y", 1,0)+D2)-C2)*24[/td][/tr]

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

3​

[/td][td]

01/01/2019​

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

12:00:00​

[/td][td]

15:00:00​

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

3.00​

[/td][td] =((IF(E3="y", 1,0)+D3)-C3)*24[/td][/tr]

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

4​

[/td][td]

01/01/2019​

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

16:00:00​

[/td][td]

15:00:00​

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

23.00​

[/td][td] =((IF(E4="y", 1,0)+D4)-C4)*24[/td][/tr]

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

5​

[/td][td]

01/01/2019​

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

23:30:00​

[/td][td]

15:00:00​

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

15.50​

[/td][td] =((IF(E5="y", 1,0)+D5)-C5)*24[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet8[/td][/tr][/table]

CAN SIMPLIFY FURTHER - by taking advantage of TRUE equates to 1 and FALSE equates to zero

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][th]

G​

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

1​

[/td][td] Date[/td][td][/td][td] Start[/td][td] End[/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][/td][td]

12:00:00​

[/td][td]

15:00:00​

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

27.00​

[/td][td] =(((E2="y")+D2)-C2)*24[/td][/tr]

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

3​

[/td][td]

01/01/2019​

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

12:00:00​

[/td][td]

15:00:00​

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

3.00​

[/td][td] =(((E3="y")+D3)-C3)*24[/td][/tr]

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

4​

[/td][td]

01/01/2019​

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

16:00:00​

[/td][td]

15:00:00​

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

23.00​

[/td][td] =(((E4="y")+D4)-C4)*24[/td][/tr]

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

5​

[/td][td]

01/01/2019​

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

23:30:00​

[/td][td]

15:00:00​

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

15.50​

[/td][td] =(((E5="y")+D5)-C5)*24[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet9[/td][/tr][/table]

 

[Yongle]

If you want to use a dropdown in column E then:

Data validation with:
Allow: LIST
Source: y (single value)
Ignore blanks: the box should be checked (ticked)

which allows the user to select y from the dropdown or delete the value in cell to alternate between the 2

 

[PlumGr8]

all awesome. I did do the drop down as that was easier for the situation and was working well with the formula. But looks like you got it with the *24 so i will try that as well .Maybe not today as i am almost done work but will def be trying them tomorrow. So, with those formulas all i, now they they are all converted to straight #'s is change the number format to 1 decimal and i should be good to go. Will report back on how it goes.

 

[Yongle]

And for the sake of completion

If it is possible to go into day 3,4 etc

With dropdown in E2 1,2,3,4,5,6 etc

and using the formula in the 3rd solution in post#17

This allows blanks so day1 can be either 1 or blank - which is useful if the majority of values are day1

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][th]

G​

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

1​

[/td][td] Date[/td][td][/td][td] Start[/td][td] End[/td][td]Which
Day[/td][td] Hours[/td][td] Formula[/td][/tr]

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

2​

[/td][td]

01/01/2019​

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

12:00:00​

[/td][td]

15:00:00​

[/td][td]

1​

[/td][td]

3.00​

[/td][td] =(((E2>1)*(E2-1)+D2)-C2)*24[/td][/tr]

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

3​

[/td][td]

01/01/2019​

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

12:00:00​

[/td][td]

15:00:00​

[/td][td]

2​

[/td][td]

27.00​

[/td][td] =(((E3>1)*(E3-1)+D3)-C3)*24[/td][/tr]

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

4​

[/td][td]

01/01/2019​

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

12:00:00​

[/td][td]

15:00:00​

[/td][td]

3​

[/td][td]

51.00​

[/td][td] =(((E4>1)*(E4-1)+D4)-C4)*24[/td][/tr]

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

5​

[/td][td]

01/01/2019​

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

12:00:00​

[/td][td]

15:00:00​

[/td][td]

4​

[/td][td]

75.00​

[/td][td] =(((E5>1)*(E5-1)+D5)-C5)*24[/td][/tr]

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

6​

[/td][td]

01/01/2019​

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

12:00:00​

[/td][td]

15:00:00​

[/td][td]

5​

[/td][td]

99.00​

[/td][td] =(((E6>1)*(E6-1)+D6)-C6)*24[/td][/tr]

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

7​

[/td][td]

01/01/2019​

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

12:00:00​

[/td][td]

15:00:00​

[/td][td]

6​

[/td][td]

123.00​

[/td][td] =(((E7>1)*(E7-1)+D7)-C7)*24[/td][/tr]

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

8​

[/td][td]

01/01/2019​

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

12:00:00​

[/td][td]

15:00:00​

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

3.00​

[/td][td] =(((E8>1)*(E8-1)+D8)-C8)*24[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet10[/td][/tr][/table]