We help IT Professionals succeed at work.

How to convert two numbers into date format

bfuchs
bfuchs asked
on
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.
Comment
Watch Question

ste5anSenior Developer

Commented:
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.
@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
Jose Gabriel Ortega CastroCEO Faru Bonon IT /Top Rated Freelancer on Upwork / Photographer
Awarded 2018
Distinguished Expert 2018

Commented:
$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

@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
Jose Gabriel Ortega CastroCEO Faru Bonon IT /Top Rated Freelancer on Upwork / Photographer
Awarded 2018
Distinguished Expert 2018

Commented:
Jum that wasn't specified in the question. Good luck :)
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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 )
Hi Pawan,

You mean I should create a function with two params?

Thanks,
Ben
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Yes please.
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
Analyst Assistant
Commented:
Try this.
IIf(IsNull(Shift_From_Hour) Or IsNull(Shift_From_Minute), "00", Format(TimeSerial(Val(Shift_From_Hour & ""), Val(Shift_From_Minute & ""), 0), "hh:mm AM/PM"))

Open in new window

@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
NorieAnalyst Assistant

Commented:
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

That worked, Thanks!
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