Excel formula

[jevans0814]

Hello,

I'm looking for an excel formula that will allow me to enter a start time under an "In" column and then in the next column labeled "Out" I would like to be able to enter the total number of hours worked and have that same "Out" cell autofill the "Out" time.  As opposed to having a third column do so.  Any help would be much appreciated!!

[#2]

Except you are happy to do this with VBA or Power Query, when you type a value into a cell containing a formula, you will overwrite the formula and there is no way to calculate back what you want.

[#3]

@jevans0814 

I'd like to preface this answer by saying I don't recommend using VBA for this. It's much better to keep the value being typed separate from the calculations. Use three columns. 

That being said, if you're set on the requested approach, you can try this:

Open the Visual Basic Editor using Alt+F11.

In the Project explorer, double-click the Sheet on which you want the calculation to happen. If you don't see the Project explore as shown in this image, go to View>Project Explorer, or alternatively press Ctrl+R.

Paste the following code into the code window.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim hours As Integer

' only do something if C2 was changed
If Target.AddressLocal = "$C$2" And Target <> "" Then
    ' get the value that was typed into cell C2
    hours = Target
    
    ' disable the events so that updating C2 to the required date will not execute this macro again
    Application.EnableEvents = False
    
    ' update C2 to be that portion of a day after the date/time in cell B2
    Target = Target.Offset(0, -1) + (hours / 24)
    
    ' enable the events
    Application.EnableEvents = True
End If

End Sub

This code will execute every time a change is made on Sheet1. If the cell that was changed is cell C2, it will execute the steps shown (i.e. it will update C2 to be x hours after B2, if x is typed into C2). 

Assumptions:

1. "In" is in cell B2

2. "Out" is in cell C2

As you can see, if I type a value into cell C2, it is updated to that many hours after the date/time in cell B2:

If you want to apply this to many rows, it can be changed somewhat easily, so let me know if that's the case.