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....

>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

> 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.

Hello, you can try this:

Sub timeval()

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

End Sub

Thanks Ian.

Perfect.
"IanKR" <ik.rip_@_ntlworld.com> wrote in message
news:uu4fhZm$(E-Mail Removed)...
> >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

> 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.