Date Time Calculation

[razaqad]

Hello friends,

I have a table for "Production Orders" ie. work orders.

there are feilds : start date , start time

based on these feilds i want to calculate the remaining time to start production.

but what i want is

if remaining time is > 1 day then
give results in the format " 1 day and 2 hrs"

elseif remaining time < 1 day then
give result in the format " 2 hrs and 24 mins"

and also current time - remaining time > 0 else result = "N/A"



how can i do this simply

 

[raskew]

Hi -

Do a search on timeconvert. Function Timeconvert() may provide some ideas.

Bob

 

[razaqad]

Public Function TimeLeft(vDate, vtime) As String

Dim DayPart As Long
Dim DayTxt As String
Dim HrPart As Long
Dim HrTxt As String
Dim MinPart As Long
Dim MinTxt As String
Dim StartTm As String
Dim HrDiff As Long
Dim MinDiff As Long

If IsNull(vDate) Or IsNull(vtime) Then
TimeLeft = ""
Else

    StartTm = vDate & " " & vtime
    HrDiff = DateDiff("h", Now(), StartTm)
    MinDiff = DateDiff("n", Now(), StartTm)
    
    Select Case HrDiff
        Case Is > 0 ' positive
            
            Select Case HrDiff  ' greater than 1 day
                Case Is > 24
                    DayPart = Int(HrDiff / 24)
                    
                    If DayPart > 1 Then DayTxt = "days" Else DayTxt = "day"
                    
                    HrPart = HrDiff - (DayPart * 24)
                    If HrPart > 1 Then HrTxt = "hrs" Else HrTxt = "hr"
                    
                    TimeLeft = DayPart & " " & DayTxt & " & " & HrPart & " " & HrTxt
                
                Case Else     ' less than  day
                    HrPart = Int(MinDiff / 60)
                    If HrPart > 1 Then HrTxt = "hrs" Else HrTxt = "hr"
                    
                    MinPart = MinDiff - (HrPart * 60)
                    MinTxt = "min"
                    
                    TimeLeft = HrPart & " " & HrTxt & " & " & MinPart & " " & MinTxt
                    
            End Select
            
        Case Else ' negative
       TimeLeft = ""
    End Select
End If
End Function

that is how i managed to do it.