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 ?

[SamT]

The code in Module1 is:

Option Explicit

Public Function DueDate(Schedule, LastDate As Date) As Date
Dim Period As String
Dim PCount As Long
Dim SchedStr As String
Dim X

SchedStr = Split(Schedule, ":")(0)

Select Case LCase(SchedStr)
Case Is = "d": Period = "w" 'w for week day. If working 7/52 use "d"
Case Is = "w": Period = "ww"
Case Is = "m": Period = "m"
Case Is = "nm": Period = "m"
Case Is = "ny": Period = "yyyy" 'Assumes "NY" = Year
Case Else: Exit Function
End Select

If Not CBool(InStr(Schedule, ":")) Then
PCount = 1
Else
PCount = Split(Schedule, ":")(1)
End If

DueDate = Format(DateAdd(Period, PCount, LastDate), "dd-mmm-yy")
End Function


the Formula in K2 and copied down is =DueDate(I2,J2)

[mok1995]

Thank you
And how to add the nm:6 ( every 6 months )

[mok1995]

And how to add a date search button ?

[SamT]

And how to add the nm:6 ( every 6 months )
It doesn't?!? As Written, it should.


And how to add a date search button ?
What do you mean?

[mok1995]

I mean i need to enter the date of tomorrow to know what are the tasks that need maintenance, because of daily tasks and other tasks we will have a full calendar to search in it, is their a method to search by full date to see the result tasks (day month year)

[snb]

you don't need one. Use conditional formatting to colour the cell that matches the present day.

[mok1995]

There are too much cells, how can i do it ?

[snb]

Is this the first time you use Excel ?

[mok1995]

Yes, it is the first time :(

[mok1995]

How will the future dates in 2021 will appear to me ?

[SamT]

The attachment is 9 years old. Up date it, make it current and attach the newest version.

[mok1995]

They need from me to work with this version of the sheet (tasks of 2012)
so i need to show the future dates with the tasks that need maintenance in each day

[mok1995]

Because there are tasks i need to do it daily, weekly, monthly, annual
so it is better to work with this version

[SamT]

Every task in this version is NINE YEARS over due. In other words, all 504 tasks must be performed tomorrow.

Also... What kind of schedule assigns ONE PERSON to complete NINETY HOURS of a daily task every day? I don't think that you have even looked at this spreadsheet.

In a previous career, I was a Master at Scheduled Maintenance. I have been in charge of a facility with 5 widely separated departments and once created a PM system for an entire multi line manufacturer. If I tell you that schedule is faulty, believe me, it is faulty. Your Uni Project is for a piddly little cement plant, which probably doesn't even have 92 employees as required by that schedule.

At the very least, change the Tasks.DateLastDone to the same date as when you attach the example. We can easily ignore the errors in Manpower and Time Needed.

[mok1995]

You are right, but you give me a code above to increase the dates, how can i see the results as today date ?

[SamT]

In your attachment/example,They are all due today.

How am I supposed to test code when they are all due today?

This term Project is supposed to test your knowledge of Excel, not mine.

snb told you how, which any first year Excel User should know.
Good luck with your Uni Professor.
Bye bye.

[mok1995]

It is DONE as I need, it works PERFECTLY

the previous dates were in 2012, and i got the new dates in 2021 and coming dates