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.
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
0
bfuchsAuthor Commented:
@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
0
Jose Gabriel Ortega CEE Solution Guide - CEO Faru Bonon ITCommented:
$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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bfuchsAuthor Commented:
@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
0
Jose Gabriel Ortega CEE Solution Guide - CEO Faru Bonon ITCommented:
Jum that wasn't specified in the question. Good luck :)
0
Pawan KumarDatabase ExpertCommented:
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 )
0
bfuchsAuthor Commented:
Hi Pawan,

You mean I should create a function with two params?

Thanks,
Ben
0
Pawan KumarDatabase ExpertCommented:
Yes please.
0
bfuchsAuthor Commented:
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
0
NorieVBA ExpertCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
@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
0
NorieVBA ExpertCommented:
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

0
bfuchsAuthor Commented:
That worked, Thanks!
0
bfuchsAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.