auto populate with date + 6 months

[flybynyte]

Hi Everyone,

I'm in desperate need for a unction/rule.

So, (this is embarrassing) a bunch of people are imputing dates in column G in a spread sheet; they are all different dates. These people need these dates plus 6 months to auto populate in column F.

For instance: column G is 04/30/18; column F needs to auto populate 10/30/2018 as soon as "enter" is hit.

I told them to type it in, or to use EDATE, i got blank stares.

Please help me.

Thank you,

Flybynyte
PS. Stay Sexy...

 

[sheetspread]

Edate does this easily, have they tried?

 

[flybynyte]

That's what i said.

They don't want to try. They want it automatic.

 

[jproffer]

Couple ways...one easy, one more "round-a-bout" way of doing it....

Excel 2013/2016

	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]6/4/2018[/TD]
[TD="align: right"]12/4/2018[/TD]
[TD="align: right"]12/4/2018[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5/2/2018[/TD]
[TD="align: right"]11/2/2018[/TD]
[TD="align: right"]11/2/2018[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/14/2018[/TD]
[TD="align: right"]7/14/2018[/TD]
[TD="align: right"]7/14/2018[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F1[/TH]
[TD="align: left"]=DATE(YEAR(E1),MONTH(E1)+6,DAY(E1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]=EDATE(E1,6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


OHHHH, I just read on down....so event code in the worksheet is better for them?

 

[jproffer]

This:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G:G")) Is Nothing Then
    Range("F" & Target.Row).Value = WorksheetFunction.EDate(Target.Value, 6)
End If
End Sub

In the worksheet module would do what they want. You'll have to format column F as Date

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G:G")) Is Nothing Then
    Target.Offset(, 1).Value = DateAdd("M", 6, Target.Value)
End If
End Sub

 

[flybynyte]

It didn't work.

Thanks for trying to help me.
I really thought there was a function/rule that those that.

 

[Jonmo1]

I really thought there was a function/rule that those that.

There is, it's called EDATE.
The fact that your users don't want to use it doesn't change that.

The users don't have to use EDATE.
You put it in for them before you give them the sheet.
They still put the dates in G, while it updates F with +6months.

 

[flybynyte]

*sighs*
I did. it was "too confusing"

 

[My Aswer Is This]

*sighs*
I did. it was "too confusing"

There have been about 4 possible solutions. And you said it did not work.

Which solution did not work.
And tell us what did it do or not do which was not what you wanted.