Date Increasing

[mok1995]

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

[#2]

@mok1995 

Does D mean daily?

[#3]

@mok1995 

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.

[#4]

@Hans Vogelaar yes, D is daily

[#5]

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

[#6]

i mean i need a formula that repeat everything or loop to reach my date

[#7 · Accepted solution]

@mok1995 

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 Function

Let'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.

[#8]

is the dates will be updated daily ?
please can you do it for sheet 2 in same file please, i need it for the two sheets
please
so everyday the dates will be updated right ?

[#9]

thank you dear, it works perfectly
thank you very very much

[#10]

@mok1995 

See the attached version.

The result will be updated automatically each day. (I entered the formula =TODAY() in N2)

[#11]

Thank you so much dear
it works perfectly :)