Date calculation based on interval

[debenay]

Hello all! I am far from an excel expert so I really need your help if possible. For work, I am trying to create a spreadsheet that's simple for our mechs to use. I'm lost as to if or how I can get the hours/date next due to auto calculate based on the PM interval type they choose from the drop down box and the date completed. So, if the mechs choose the 30 day interval and type in the date they completed it, how can I get the hours next due to reflect 30 days from that date?

And I need that to change for every interval they choose, so 90, 120, 360 days as well as hours (100, 200, 500 hours) etc.

Thank you for any help or suggestions

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Equipment[/TD]
[TD]PM Interval Type[/TD]
[TD]Date Completed[/TD]
[TD]Interval Completed[/TD]
[TD]Date/Hours Next Due[/TD]
[/TR]
[TR]
[TD]Air Compressor[/TD]
[TD]30 day (this will be a drop down box with various intervals to chose from; 30, 60,90 day 100, 200 hours etc)[/TD]
[TD]8/14/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

If in your cell B2 ... you are adding a drop down (data validation) with all your possible numbers : 30, 60, 90, 100, 200, etc ...

and if you do have a date such as 8/14/18 in cell C2 .... you can have in cell E2 the following formula :

=C2+B2

Then you only need to format your cell E2 as a date ...

Hope this will help

 

[debenay]

Wow that really was pretty simple haha My only question is IF my boss says I need to keep the "day" or "hour" description in the drop down, is there a way to calculate it with that? Also, if a date isn't entered into Column C, how do I prevent 1/0/1900 from populating in the Date/hours next due cells?

 

[James006]

Glad you are heading in the right direction ...

To account for your remarks, you could use the following formula :

=IF(C2,IF(RIGHT(A2,4)="days",VALUE(SUBSTITUTE(A2," days","")),A2)+C2,"")

HTH

 

[debenay]

James 006, thank you! Is there ANY way that excel would know if the PM is an hour or date interval without us having a separate column for them or putting in individual formulas for each piece of equipment?

My boss would like the guys and gals to be able to just put the info in or add equipment without them worrying about formulas...something that can just be copied and applied regardless if it's hours or days?

I hope that made sense haha

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Equipment[/TD]
[TD]PM Interval[/TD]
[TD]Current Hours/Mileage[/TD]
[TD]Date Completed[/TD]
[TD]Date/Hours Next Due[/TD]
[/TR]
[TR]
[TD]Air Compressor[/TD]
[TD]30 Day[/TD]
[TD][/TD]
[TD]8/2/18[/TD]
[TD]=B2+D2 (minus day text)[/TD]
[/TR]
[TR]
[TD]Golf Cart[/TD]
[TD]250 Mile[/TD]
[TD]450 [/TD]
[TD]6/2/18[/TD]
[TD]=C2+250[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

Everything hinges on your definition of your PM Interval ... :wink:

Do you have a complete list of all your items ...???