multiplying using time??

[Cirrostratus]

I have a query where I'm trying to calculate when an order is expected to be completed. I have a lot size and a time standard with an efficiency for production. The result gives me a text string of time but I need it to be in the hh:mm format without doing a custom function (I later use this in a pivot table in excel).

Right now I have:

[Lot_Size]*(([TimeStandard])*(100/[Efficiency]))+[StartTime]

The lot size is a whole number
The time standard is a hh:mm format
The efficiency is a whole number
The StartTime is hh:mm format

As an example, what I need is:

10 * (00:05 * (100/110))+10:00 = 10:45

But what I'm getting is 00:4510:00, which is a bit odd.. any ideas??

 

[pr2-eugin]

You need to use the DateAdd function.. I am writing this based on what I see..

DateAdd("n", [Lot_Size]*(Minute([TimeStandard]) * (100/[Efficiency])), [StartTime])

The example you gave would give 10:45:00..

 

[Cirrostratus]

That's perfect, the Minutes part I hadn't thought about, thanks!

What about a count down, so If I want to see how many minutes I have left on an order?

A DateAdd wouldn't work for that would it?

Thanks Again!

 

[pr2-eugin]

How about DateDiff?

 

[Cirrostratus]

Thanks again for all the help!! The query is still causing problems for me, here's what I got right now:

The first part, the DueTime looks like this (it works fine without any problems)

DueTime: Format(DateAdd("n";IIf([PauseDuration]>0;[PauseDuration];0);DateAdd("n";([Lot_Size]*Minute([TimeStandard]*(100/[Efficiency])));[StartTime]));"Short Time")

But the TimeLeft calculation is causing a problem:

TimeLeft: IIf(Format(Now();"Short Time")>[DueTime];"0:00";DateDiff("n";Format(Now();"Short Time");DateAdd("n";[PauseDuration];DateAdd("n";([Lot_Size]*Minute([TimeStandard]*(100/[Efficiency])));[StartTime]))) & Format(DateDiff("n";DateAdd("n";[PauseDuration];Format(Now();"Short Time"));DateAdd("n";[Lot_Size]*Minute([TimeStandard]*(100/[Efficiency])));[ST])) Mod 60;":00"))

The results for the Time Left are either an #error or minutes showing up as hours so like 279:00 when it should be 4:39. Any ideas how to fix this?

Many thanks again for all the help!!

 

[Cirrostratus]

Ok, I managed to fix it with just a few small tweaks (the PauseDuration was causing problems as well as the general calc). But, I've got one small issue that still needs to be figured out if someone can help?

The code is:

TimeLeft: IIf(Format(Now();"Short Time")>[DueTime];"0:00";DateDiff("n";Format(Now();"Short Time");DateAdd("n";IIf([PauseDuration]>0;[PauseDuration];0);DateAdd("n";([Lot_Size]*Minute([TimeStandard]*(100/[Efficiency])));[StartTime])))\60 & ":" & Format(DateDiff("n";DateAdd("n";IIf([PauseDuration]>0;[PauseDuration];0);Format(Now();"Short Time"));DateAdd("n";([Lot_Size]*Minute([TimeStandard]*(100/[Efficiency])));[StartTime])) Mod 60))

The problem I get is when the minutes are single digits, so if there is say 5:08 (5 hours 8 minutes) the result actually looks like 5:8, so it leaves out the leading 0 from the minutes. Its really odd.. Any suggestions??

Any help is appreciated!!