asked on

How to convert two numbers into date format

Hi Experts,

I have two numbers fields, Shift_From_Hour and Shift_From_Minute
They store hours and minutes.

How do I display them in "01:45 PM" format, taking in consideration that the minutes field can be null or zero, which in that case it should be 00.

In addition if both are null or the hour field is null, it should just be be blank.

ste5an

Use TimeSerial(Hour, Minute, Second). E.g.

ShiftFrom: TimeSerial(Shift_From_Hour, Nz(Shift_From_Minute, 0), 0)

Open in new window

in a query.

bfuchs

ASKER

@ste5an,

That gives me an extra two digits for the seconds.
How can I get rid of them?
Also when both are null I get #Error.

Thanks,
Ben

J0rtIT

$isHourNull = [String]::IsNullOrEmpty($Shift_From_Hour)
$isMinuteNull = [String]::IsNullOrEmpty($Shift_From_Minute)

#4 cases
$today=[Datetime]::Now
if($isHourNull -and $isMinuteNull){
    $result=New-Object Datetime -ArgumentList @($today.Year,$today.Month,$today.Day,0,0,0)
}
elseif(!($isHourNull) -and $isMinuteNull){
    $result=New-Object Datetime -ArgumentList @($today.Year,$today.Month,$today.Day,$Shift_From_Hour,0,0)
}
elseif(!($isMinuteNull) -and $isHourNull){
    $result=New-Object Datetime -ArgumentList @($today.Year,$today.Month,$today.Day,0,$Shift_From_Minute,0)
}
else{
    $result= New-Object Datetime -ArgumentList @($today.Year,$today.Month,$today.Day,$Shift_From_Hour,$Shift_From_Minute,0)
}
Write-Host $result.ToString("t")

Open in new window

bfuchs

ASKER

@Jose,

I need this in VB/A, as this is to be used as a control source for a control in an Access report.

Thanks,
Ben

J0rtIT

Jum that wasn't specified in the question. Good luck :)

Pawan Kumar

Please try like this -

Dim LString As String
Dim LArray() As String

LString = Split (TimeSerial(Nz(Shift_From_Hour,0), Nz(Shift_From_Minute, 0), 0), ".")
LArray = Split(LString)

LArray(0) + ':' + LArray(1) + ' ' + RIGHT( LArray(2), 2 )

bfuchs

ASKER

Hi Pawan,

You mean I should create a function with two params?

Thanks,
Ben

Pawan Kumar

Yes please.

bfuchs

ASKER

Trying the following and getting error "Type mismatch"

Function TestPawanDate(i1 As Variant, i2 As Variant)
Dim LString As String
Dim LArray() As String

LString = Split(TimeSerial(Nz(Shift_From_Hour, 0), Nz(Shift_From_Minute, 0), 0), ".")
LArray = Split(LString)

TestPawanDate = LArray(0) + ":" + LArray(1) + " " + Right(LArray(2), 2)
End Function

Open in new window

Thanks,
Ben

ASKER CERTIFIED SOLUTION

Norie

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

bfuchs

ASKER

@Norie,

Okay, that solved the extra seconds issue.

However now it always returns "00", even if has hour and no min (which in that case it should be the hour with 00)
or in case if both are nulls then it should be blank.

How can these be adjusted?

Thanks,
Ben

Norie

Not sure I'm following all your specifications but try this.

x = IIf(IsNull(Shift_From_Hour) And IsNull(Shift_From_Minute), "", Format(TimeSerial(Val(Nz(Shift_From_Hour, "00") & ""), Val(Nz(Shift_From_Minute) & ""), 0), "hh:mm AM/PM"))

Open in new window

bfuchs

ASKER

That worked, Thanks!

bfuchs

ASKER

I simply adjusted for the following

IIf(nz([Shift_From_Hour],0)=0,"",Format(TimeSerial(Val([Shift_From_Hour] & ""),Val([Shift_From_Minute] & ""),0),"hh:mm AM/PM"))

Open in new window

Thanks,
Ben