Forum Discussion
Date Increasing
Hello all
I have an excel sheet containing information for many machines that needs maintenance, each machine has a starting date of maintenance and the maintenance will be done repetitively in a specific frequency ( some are daily, weekly, monthly, semi annual or annual and many frequencies), I need to put the date of today or tomorrow or any coming date to see what are the machines that need maintenance at that date and the information of each machine
I will link the file, the task schedule is the frequency (NM:1 is monthly....) the task last date done is the reference date of each machine
how can I do it ?
is it visual basic or formulas?
the dates are in 2012, but I need to increase dates to reach today as in 2021
Custom function, to be copied into a new module in the Visual Basic Editor:
Function NextDate(DateLast As Date, Schedule As String, DateFrom As Date) As Date Dim n As Long Dim m As Long Select Case Schedule Case "D" NextDate = DateFrom Case "W" n = (DateFrom - DateLast) \ 7 NextDate = DateLast + 7 * n If NextDate < DateFrom Then NextDate = NextDate + 7 End If Case "W2" n = (DateFrom - DateLast) \ 14 NextDate = DateLast + 14 * n If NextDate < DateFrom Then NextDate = NextDate + 14 End If Case "M" n = DateDiff("m", DateLast, DateFrom) NextDate = DateAdd("m", n, DateLast) If NextDate < DateFrom Then NextDate = DateAdd("m", n + 1, DateLast) End If Case "NM:2", "NM:3", "NM:4", "NM:5", "NM:6", "NM:7", "NM:8", "NM:9", "NM:10", "NM:11" m = Split(Schedule, ":")(1) n = m * (DateDiff("m", DateLast, DateFrom) \ m) NextDate = DateAdd("m", n, DateLast) If NextDate < DateFrom Then NextDate = DateAdd("m", n + m, DateLast) End If Case "NY:1" n = DateDiff("yyyy", DateLast, DateFrom) NextDate = DateAdd("yyyy", n, DateLast) If NextDate < DateFrom Then NextDate = DateAdd("yyyy", n + 1, DateLast) End If Case "NY:2" n = DateDiff("yyyy", DateLast, DateFrom) If n Mod 2 Then n = n - 1 NextDate = DateAdd("yyyy", n, DateLast) If NextDate < DateFrom Then NextDate = DateAdd("yyyy", n + 2, DateLast) End If End Select End FunctionLet's say you enter a date in N2.
Enter the following formula in K2:
=NextDate(J2,I2,$N$2)
Fill down to the last row with data.
You can now easily filter the data on column K for today or next month or whatever.
See the attached workbook, now a macro-enabled workbook. Make sure that you allow macros when you open it.
10 Replies
- i mean i need a formula that repeat everything or loop to reach my date
Excel formulas - maintenance plan
In the file inserted in this message you will find formulas that can be of use for your project.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
- it is okay, but i can do this by duedate formula, but the problem is the last dates are in 2012 and there are daily, weekly tasks, and i need to reach my date in 2021 to see the tasks that need maintenance,
if i will do due date formula or as your file it will take too long time because i have about 1000 tasks
Does D mean daily?
HansVogelaar yes, D is daily
Custom function, to be copied into a new module in the Visual Basic Editor:
Function NextDate(DateLast As Date, Schedule As String, DateFrom As Date) As Date Dim n As Long Dim m As Long Select Case Schedule Case "D" NextDate = DateFrom Case "W" n = (DateFrom - DateLast) \ 7 NextDate = DateLast + 7 * n If NextDate < DateFrom Then NextDate = NextDate + 7 End If Case "W2" n = (DateFrom - DateLast) \ 14 NextDate = DateLast + 14 * n If NextDate < DateFrom Then NextDate = NextDate + 14 End If Case "M" n = DateDiff("m", DateLast, DateFrom) NextDate = DateAdd("m", n, DateLast) If NextDate < DateFrom Then NextDate = DateAdd("m", n + 1, DateLast) End If Case "NM:2", "NM:3", "NM:4", "NM:5", "NM:6", "NM:7", "NM:8", "NM:9", "NM:10", "NM:11" m = Split(Schedule, ":")(1) n = m * (DateDiff("m", DateLast, DateFrom) \ m) NextDate = DateAdd("m", n, DateLast) If NextDate < DateFrom Then NextDate = DateAdd("m", n + m, DateLast) End If Case "NY:1" n = DateDiff("yyyy", DateLast, DateFrom) NextDate = DateAdd("yyyy", n, DateLast) If NextDate < DateFrom Then NextDate = DateAdd("yyyy", n + 1, DateLast) End If Case "NY:2" n = DateDiff("yyyy", DateLast, DateFrom) If n Mod 2 Then n = n - 1 NextDate = DateAdd("yyyy", n, DateLast) If NextDate < DateFrom Then NextDate = DateAdd("yyyy", n + 2, DateLast) End If End Select End FunctionLet's say you enter a date in N2.
Enter the following formula in K2:
=NextDate(J2,I2,$N$2)
Fill down to the last row with data.
You can now easily filter the data on column K for today or next month or whatever.
See the attached workbook, now a macro-enabled workbook. Make sure that you allow macros when you open it.
