change integer to time by VBA

[Eyeson15]

Hello everyone
I'm going little nuts here

A1 has value 15 in it. (integer, no decimals)
I need VBA to dim this value as hh:mm:ss and then subtract 10minutes from it

How close am I?

Sub timechange
Dim aaa as Date
aaa = Range("A1").value + Timeserial(0,-10,0)

Thank you in advance,

James

 

[Norie]

James

What time does the 15 in A1 represent? 15 hours? minutes? seconds?

 

[JoeMo]

What result do you expect?

 

[Eyeson15]

James

What time does the 15 in A1 represent? 15 hours? minutes? seconds?

Hi there

ahhh 15 represent 3pm. 16 represents 4pm and so on.

so if A1 is 15 then I need 3pm - 10 minutes to continue working with other macros

 

[JoeMo]

Hi there

ahhh 15 represent 3pm. 16 represents 4pm and so on.

so if A1 is 15 then I need 3pm - 10 minutes to continue working with other macros

Does this do what you want?

Sub Take10()
Const T As Variant = 10
aaa = Range("A1").Value / 24 - TimeSerial(0, T, 0)
MsgBox Format(aaa, "hh:mm:ss AM/PM")
End Sub

 

[Norie]

Perhaps.

    aaa = TimeSerial(Range("A1").Value, 0, 0) - TimeSerial(0, 10, 0)

 

[Eyeson15]

Does this do what you want?

Sub Take10()
Const T As Variant = 10
aaa = Range("A1").Value / 24 - TimeSerial(0, T, 0)
MsgBox Format(aaa, "hh:mm:ss AM/PM")
End Sub

Oh god you are a heaven sent because that worked perfectly. I can take it from here but thank you so much.

I will think about you before I sleep tonight.

James

 

[JoeMo]

Oh god you are a heaven sent because that worked perfectly. I can take it from here but thank you so much.

I will think about you before I sleep tonight.

James

You are welcome - thanks for the reply.

 

[Eyeson15]

Perhaps.

    aaa = TimeSerial(Range("A1").Value, 0, 0) - TimeSerial(0, 10, 0)

This works too! Thank you for your help

 

[mikerickson]

Dim xVal as Date

xVal = TimeValue((Range("A1").Value - 1) & ":50:00")

MsgBox xVal