Adding time by a Numerical Value

[Corey]

I have a cell with a format of "hh:mm AM/PM" and
another cell in a numerical format of "0.75".

I want to using VBA, add the numerical value to the time to create a new
time, such as:

A1= 7:30 AM
B1 = 0.75 ' 45mins
C1= 8:15 AM ' Result.

I cannot seem to get the correct syntax to add the value to give me the
desired result.

If i format the numerical value as "hh:mm" it changes from 0.75 to 18:00 ?

How can i do this:

Corey....

 

[IanKR]

I have a cell with a format of "hh:mm AM/PM" and

another cell in a numerical format of "0.75".

I want to using VBA, add the numerical value to the time to create a new
time, such as:

A1= 7:30 AM
B1 = 0.75 ' 45mins
C1= 8:15 AM ' Result.

I cannot seem to get the correct syntax to add the value to give me the
desired result.

If i format the numerical value as "hh:mm" it changes from 0.75 to 18:00 ?

How can i do this:

Corey....

This worked for me:

Sub AddTimeAsNumber()
Dim num As Integer
num = Hour(Range("A1")) * 60 _
+ Minute(Range("A1")) _
+ Range("B1") * 60
Range("C1") = Format(WorksheetFunction.RoundDown(num / 60, 0) _
& ":" & num Mod 60, "h:mm\ AM/PM")
End Sub

.... but there's probably a more elegant way!

Ian

 

[IanKR]

If i format the numerical value as "hh:mm" it changes from 0.75 to 18:00 ?

That's because 18:00 (6pm) is three-quarters of the way through the day.

 

[GTVT06]

Hello, you can try this:

Sub timeval()

Range("C1").Value = Range("A1") + TimeSerial(0, Minute("12:" &
Range("B1") * 60), 0)

End Sub

 

[Corey]

Thanks Ian.

Perfect.

This worked for me:

Sub AddTimeAsNumber()
Dim num As Integer
num = Hour(Range("A1")) * 60 _
+ Minute(Range("A1")) _
+ Range("B1") * 60
Range("C1") = Format(WorksheetFunction.RoundDown(num / 60, 0) _
& ":" & num Mod 60, "h:mm\ AM/PM")
End Sub

... but there's probably a more elegant way!

Ian

 

[IanKR]

Hello, you can try this:

Sub timeval()

Range("C1").Value = Range("A1") + TimeSerial(0, Minute("12:" &
Range("B1") * 60), 0)

End Sub

!!

I knew somebody would come up with a slicker formula then mine. I didn't
know about TimeSerial - equivalent to DateSerial, of course.