Convert Hrs to Days and give the Date...

[jw5107]

I am trying to figure out when a part on a aircraft is "going" to fail. Meaning if the part manufacture is suggestioning that this part (will say a pump for now) will last 1300hrs from the time it was installed. Is there a way to figure out when 1300hrs will be?? Something like a result of 54 days (in one field) from the date of installation , which will be Nov-6 (in another field)?

I have been looking and looking for this conversion to only find no joy!!!

Thanks in advance,
jw

 

[EBGreen]

I would start by looking at DateSerial

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[SkipVought]

Hi,

If it takes 1300 hours, is that at a rate of 24 hours per day (3 shifts) or 8 hours per day (1 shift) or applying some realization it might be at a rate of 6.245 hours psr day.

???

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[RJFost]

Or use DateAdd, like

Format(DateAdd("h",1300,#11/06/04#), "mm/dd/yy")

which will return 12/30/04. If you skip the Format(), it returns 12/30/04 04:00 (it assumed a 00:00 start time on 11/06).

That what you had in mind?

 

[EBGreen]

Good point Skip. Unless the aircraft is functioning 100% of the time, simply adding 1300 won't work. If however you know that say on average an aircraft is operational 8 hours per day (33%), then adding 1300/.33 or ~3939 should work as an approximation.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[SkipVought]

My perspective was from aircraft manufacturing. I missed that this was OPERATION hours.

I suppose that you take the average flight hours per day/week/month, and calculate a projected Next PM Date.
[tt]
StartDt = InstallDate
InHrs = 1300
NextPMDate = StartDt + CLng(DateSerial(1900, InHrs / AvgFltHrsPerMonth, 1)) - 1
[/tt]


Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[jw5107]

To all that replied:

Yes you are all right!!

The aircraft that the pump is installed on may only operate a few hrs a day - 4:53 (4hrs 53mins). Some how I need to take the 1300hrs and calculate WHEN the aircraft will reach 1300hrs based on 4:53 of operation per day....
I need to know what day of the month that will be so I can hand over a report each week/month to the boss man that will let him know WHAT parts are gonna fail WHEN for each aircraft we operate. This way we can shift our maintenance to a "scheduled" removal instead of a "unscheduled" removal. Big difference in operating cost!!!

What I know about the aircraft and the part installed is:
Install Date - 7/1/04
MTBUR of part - 1300hrs (Mean Time Between Unit Removal)
Aircraft Hrs/Day - 4:53

What I need to know:
Scheduled Removal Date

Thanks for all the help!!!
jw

 

[EBGreen]

I did this quickly in vbScript, but it should easily convert to VBA:

' Install Date - 7/1/04
' MTBUR of part - 1300hrs (Mean Time Between Unit Removal)
' Aircraft Hrs/Day - 4:53

Dim dteInstallDate
Dim dMTBUR
Dim nHourUsage
Dim dMinUsage
Dim fUsageFactor
Dim dtePMRDate

dteInstallDate = "7/1/04"
dMTBUR = 1300
nHourUsage = 4
dMinUsage = 53

dMinUsage = nHourUsage * 60 + dMinUsage
fUsageFactor = dMinUsage/(24 * 60)
MsgBox dMTBUR/fUsageFactor

dtePMRDate = DateAdd("h", dMTBUR/fUsageFactor, dteInstallDate)

MsgBox dtePMRDate

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[jw5107]

TomThumbKP,

WOW!! Thanks for the help!!!

One question: How do I apply all this??
Do I copy into a module, and call it from a query? If so, how exactly??? Sorry that I need my hand held a little bit, just need some more direction...

Awesome - thanks!!! I am anxious to see what this does!!
jw

 

[EBGreen]

I may not be the best person to ask this question of, but I would do three things.
1) Add a field to the table where the install date is held to hold the PMR date for the part.
2) Create a macro that will query every part and update the PMR date based on the install date for that part.
3) Wherever you handle adding a new part or updating an existing part, have it also update the PMR date.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[jw5107]

TomThumbKP,

Oh man - I still am confused. I kinda get what your sayin', but I don't....
I am not sure what to put in the "update to" row of the update query. The install date is gonna be different for every part/aircraft and the HrsperDay is gonna be differnt fore each aircraft. The MTBUR is gonna be different for each part. Not sure about part of the code you supplied -

dteInstallDate = "7/1/04"
dMTBUR = 1300
nHourUsage = 4
dMinUsage = 53

Are these constants? I need 4th grade level!!!

Thanks for the help once again!!
jw

 

[EBGreen]

I will try to spend some time today working up some examples. There is probably someone around that knows a better way and they will hopefully post it.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[jw5107]

TomThumbKP,

Great thanks!! I will keep working with what you supplied. Hopefully somebody can break it down for me!!!

Thanks again!!
jw

 

[jw5107]

To whom in which can break it down,

Below is code I am working with provided by TomThumbKP. I put it in a Module and got it work when I press F5 within the module. I tried creating a query with a field like - RemoveDate: MTBURDate() and it's not quite getting me what I would like. The result is always in the form of a MsgBox, and I need for it to be in a query and/or form. The ultimate would be in a form due to the fact of multi users....
I have a 2 tables with the install date info and another with the aircraft Hrs/Day info.... Tbls and Flds are named:

TblUsage - FldInstallDate, FldTail, FldPartNum.
TblAircraftUltization - FldTail, FldAircraftDays(how many days Aircraft is used per month), FldAircraftHrs/Day.

I am tryin' to get this as automated as possible. I am also tryin' to get addtional info about the part being installed....If we can figure out the date inwhich the part is to fail, then I will need fields with how many days that is, how many hrs the part has been OnWing, and how many operating hrs till the part is to fail.

Awesome stuff!!!!! I sure to appreciate any help in getting this hashed out!!!! Thanks in advance!!!!!!!!
jw

 

[jw5107]

To whom in which can break it down,

Below is code I am working with provided by TomThumbKP. I put it in a Module and got it work when I press F5 within the module. I tried creating a query with a field like - RemoveDate: MTBURDate() and it's not quite getting me what I would like. The result is always in the form of a MsgBox, and I need for it to be in a query and/or form. The ultimate would be in a form due to the fact of multi users....
I have a 2 tables with the install date info and another with the aircraft Hrs/Day info.... Tbls and Flds are named:

TblUsage - FldInstallDate, FldTail, FldPartNum.
TblAircraftUltization - FldTail, FldAircraftDays(how many days Aircraft is used per month), FldAircraftHrs/Day.

I am tryin' to get this as automated as possible. I am also tryin' to get addtional info about the part being installed....If we can figure out the date inwhich the part is to fail, then I will need fields with how many days that is, how many hrs the part has been OnWing, and how many operating hrs till the part is to fail.

Awesome stuff!!!!! I sure to appreciate any help in getting this hashed out!!!! Thanks in advance!!!!!!!!


Public Function MTBURDate()
Dim dteInstallDate
Dim dMTBUR
Dim nHourUsage
Dim dMinUsage
Dim fUsageFactor
Dim dtePMRDate

dteInstallDate = "8/8/03"
dMTBUR = 4200
nHourUsage = 4
dMinUsage = 21

dMinUsage = nHourUsage * 60 + dMinUsage
fUsageFactor = dMinUsage / (24 * 60)
MsgBox dMTBUR / fUsageFactor

dtePMRDate = DateAdd("h", dMTBUR / fUsageFactor, dteInstallDate)

MsgBox dtePMRDate
End Function

 

[jw5107]

Can anybody help me out??? If there are any other questions regarding the data at hand or what not - please let me know!!!

Thanks
jw

 

[SkipVought]

you function is not returning a value

Public Function MTBURDate()
Dim dteInstallDate
Dim dMTBUR
Dim nHourUsage
Dim dMinUsage
Dim fUsageFactor
Dim dtePMRDate

dteInstallDate = "8/8/03"
dMTBUR = 4200
nHourUsage = 4
dMinUsage = 21

dMinUsage = nHourUsage * 60 + dMinUsage
fUsageFactor = dMinUsage / (24 * 60)
MsgBox dMTBUR / fUsageFactor

dtePMRDate = DateAdd("h", dMTBUR / fUsageFactor, dteInstallDate)

MTBURDate = dtePMRDate
End Function

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[jw5107]

Skip,

Thanks for the fast response...

How can can I verify that this is working correctly??
I need to be able to see exactly how many days it will take for a part to reach its MTBUR based on an aircraft operating just a few hrs a day.... Also, the value ends up being a date and time - I just need date. How do I get rid of the time part of the value? Right now I have the HrsperDay field a date/time type. I see that this code is dividing the MTBUR by the HrsperDay, can you explain this? Not clear to me. Surely this has something to do with getting the amount of time it will take to reach MTBUR????

Thanks for your help!!
jw

 

[SkipVought]

How can can I verify that this is working correctly??

You can set up a test. I didn't notice that the function that had been posted did not have a date passed as an argument. It should!

Sub test()
   MsgBox Format(MTBURDate(#1/1/2004#, 1000, 4, 21), "yyyy-mm-dd")
End Sub
Public Function MTBURDate(dteInstallDate As Date, dMTBUR As Integer, _
nHourUsage As Integer, dMinUsage As Integer) As Date
   Dim fUsageFactor
   Dim dtePMRDate
   
   dMinUsage = nHourUsage * 60 + dMinUsage
   fUsageFactor = dMinUsage / (24 * 60)
   
   MTBURDate = DateAdd("h", dMTBUR / fUsageFactor, dteInstallDate)
End Function

Also, the value ends up being a date and time - I just need date. How do I get rid of the time part of the value?

It is simply a Format issue as I illustrate in the test.

Right now I have the HrsperDay field a date/time type. I see that this code is dividing the MTBUR by the HrsperDay, can you explain this? Not clear to me. Surely this has something to do with getting the amount of time it will take to reach MTBUR????

I don't know what dMinUsage is supposed to be, but in general...
[tt]
dMTBUR is HOURS of operation (INTEGER)
nHourUsage is HOURS PER DAY of operation (INTEGER)
so

dMTBUR / nHourUsage
gives you DAYS of operation (INTEGER)

So when you add DAYS of operation to the Install Date you get a Date.
[/tt]



Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]