VBA Coding, need help!

BBC123

New Member
Joined
Aug 28, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi!
I am currently trying to figure out a VBA code (I’m not super familiar, but can grasp concept with little experience I have):
The coding is for a training tracking sheet.
I have a date in one cell, that will get over-written each year with the new training date.
the next cell over, Calculates the expiry date.. Which is the 1st of the next month, in the next year. However, if the training is completed within 90 days of the original expiry date, then the original expiry date stays the same, but adds a year only.
Is there anyone willing to help me create a VBA code for this? It seemed like it should be an easy fix, but I just can’t wrap my head around it.
Example; if training was done on February 4, 2020.. the expiry would be March 1, 2021 BUT if the training was completed January 2, 2021... the expiry would then stay at March 1, 2022 (just add a year)

thanks in advance, I’ve been stuck on this for a week now and I’m really hoping It’ll help my workload once Its done :)
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,723
What happens if not within 90 days? what happens if after the expiry?

This will add a month and a year to a date, and make it the 1st of the month.

VBA Code:
Sub adddate()

Dim trainingdate As Date

trainingdate = Range("A1")
expiry = DateSerial(Year(DateAdd("yyyy", 1, trainingdate)), Month(DateAdd("m", 1, trainingdate)), 1)

End Sub
 
Last edited:

BBC123

New Member
Joined
Aug 28, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
What happens if not within 90 days? what happens if after the expiry?

This will add a month and a year to a date, and make it the 1st of the month.

VBA Code:
Sub adddate()

Dim trainingdate As Date

trainingdate = Range("A1")
expiry = DateSerial(Year(DateAdd("yyyy", 1, trainingdate)), Month(DateAdd("m", 1, trainingdate)), 1)

End Sub

hi, I have figured out how to code the 1st of the next month, but it’s just the 90 day clause that I’m stuck on.
If the training is done within 90 days of the expiry, then the original month and day stay the same, but adds one year.

If it’s not done within 90 days, then it regularly calculates 1st of next month, next year.

if after expiry, it calculates 1st of next month, next year.

Any ideas from here?
 

BBC123

New Member
Joined
Aug 28, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Just to add one more thing.. I am meaning to say that if the training is done within 90 days before the expiry date, then the original expiry date stays the same but adds a year.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,723
Is this doing what you want?

If less training date is less than 90 days before expiry then add a Year and a Month (and change to the 1st) to the training date for the expiry

If the training date A2 is greater than the expiry B2 then B2 + 1 Year (same if less than 90)


VBA Code:
Sub adddate()

trainingdate = Range("A2")
expiry = Range("B2")

If expiry - trainingdate < 90 Then
Range("B2") = DateSerial(Year(DateAdd("yyyy", 1, expiry)), Month(expiry), 1)
Else
Range("B2") = DateSerial(Year(DateAdd("yyyy", 1, trainingdate)), Month(DateAdd("m", 1, trainingdate)), 1)
End If

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,970
Messages
5,627,923
Members
416,282
Latest member
fchagas97

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top