Add workday from the value of the cell above.

[Cuylaerts]

Hello everybody,

How can i make a formula that adds a workday of the date givin by the cell above?
something like this?
i want to type the formula in a sheet (no vba.)

=Workday(Activecell.offset(-1,0),1) ?

i can't seem to figure it out.

ty.

 

[Joe4]

Activecell.offset(-1,0) is VBA syntax (which you claim to not want).

Just use the address of the cell above it.
For example, if this formula is being placed in cell E10, just use:
=Workday(E9,1)
If you copy the formula to any other cell, Excel will automatically adjust it and use the address of the cell above where you are placing the formula.

 

[Cuylaerts]

that doesn't really works for me,
in my vba i have a insert row function, and when this happens i want my date in the copied row go +1
if i try to do this with vba it works with one row, but when i add 2 the first row added gets +1 but the 2nd one gets +2 which i want reversed.

eg. how it is: ____________ how i want it.
7-8-16 (d/m/y) _____________ 7-8-16
9-8-16 => 2nd row added_____ 8-8-16
8-8-16 => 1st row added _____ 9-8-16

as u see the date is not in order, i cant use a sort function because that is already used in another column
so i wanted to us the formula that i described so it just adds 1 from the cell value above.

i hope this is clear enough

 

[Joe4]

I am kind of confused.

Originally you said:

i want to type the formula in a sheet (no vba.)

But now you are saying:

in my vba i have a insert row function, and when this happens i want my date in the copied row go +1
if i try to do this with vba it works with one row, but when i add 2 the first row added gets +1 but the 2nd one gets +2 which i want reversed.

So do you want to use VBA or not?

If you currently have VBA code, please post it.

 

[Cuylaerts]

Well if there is a formula for it in just excel sheet i know how to fix it, that's why i asked for a non vba solution.

this is my vba code so far.


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveCell.Offset(0, 0).Select
ActiveWorkbook.Worksheets("Hoofd-Overzicht").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hoofd-Overzicht").Sort.SortFields.Add Key:=Range("E2:E735") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Hoofd-Overzicht").Sort
.SetRange Range("A2:N735")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With
If Target.Column = 6 Then
If Target > 15 Then
Application.EnableEvents = False
Dim cp As Integer
cp = Range("N" & Target.Row)
For x = 1 To ((Range("F" & Target.Row) - 1) / ActiveWorkbook.Worksheets("Ignore").Range("C20")) - cp
Target.EntireRow.Copy
Range("A" & Target.Row + 1).Insert Shift:=x1Down
Range("C" & Target.Row + 1).Formula = ("Zie Boven")
Range("F" & Target.Row + 1).Formula = ("")
Range("G" & Target.Row + 1).Formula = ActiveWorkbook.Worksheets("Ignore").Range("C22")
Range("N" & Target.Row) = Range("n" & Target.Row) + 1
Application.CutCopyMode = False
Next x
End If
End If

Fat line: that would have been my redirection to the formula.
is this good? (i'm only a beginner)

 

[Jonmo1]

i want to type the formula in a sheet (no vba.)

=Workday(Activecell.offset(-1,0),1) ?

Here's my literal interpretation of that

Type =Workday(
Press UP Arrow
Type ,1)
Press Enter

 

[Joe4]

If you turn on the Macro Recorder, and record yourself entering the formula want in any cell (referencing the cell above it), and then stop the Recorder, it will give you what I think you are looking for - a formula that uses Relative Range References to reference the cell above it.

So if you do that, you will see it should look something like this:

[B]Range("G" & Target.Row + 1).[/B][COLOR=#ff0000]FormulaR1C1 = "=WORKDAY(R[-1]C,1)"[/COLOR]

 

[Cuylaerts]

not working for my purpose,
my cell always changes from location, it should always add a day from the date above the cell. not a specified cell.

 

[Joe4]

not working for my purpose,
my cell always changes from location, it should always add a day from the date above the cell. not a specified cell.

I don't understand. Where do you see a specified cell in this formula?

[COLOR=#ff0000]"=WORKDAY(R[-1]C,1)"[/COLOR]

Do you understand how R1C1 notation works?
R[-1] tells it that whatever cell you are placing the formula in, use the row reference that is one row above it. It is totally dynamic. There is no hard-coded row reference.
C tells it to use the same column reference as you are placing the formula in.

So, if you were placing the formula in cell G10, it will use G9 in your formula.
If you were placing the formula in B100, it will use B99 in your formula.

If I am understanding you correctly, that sounds like exactly what you are looking for.
If it isn't, you will need to try to explain it again more clearly.

 

[Cuylaerts]

Ty , this is what i wanted!
many thx!