convert number to time format

[Av8tordude]

I'm looking up the city UTC time zone. Some cities have negative numbers versus positive numbers. When it is found, I trying to convert the number into a time format (i.e. -5.5 = -5:30). However, when a number is negative and I convert it into a time format, the number is not being displayed a negative time format (i.e. -5:30). The code I'm using is...

TimeValue(Format(Application.WorksheetFunction.VLookup(cboDep, Range("CD9:CE149"), 2, False) / 24, "hh:mm"))

Also, I'm trying to use the UTC time zone number to either add or subtract the time being used. (i.e. 13:00 + (-5:30 UTC) = 07:30). And this is were I'm frustrated to the point of seeking help. haha

TimeValue(txtDepTm) + TimeValue(Format(Application.WorksheetFunction.VLookup(cboDep, Range("CD9:CE149"), 2, False) / 24, "hh:mm"))

So I'm asking... HEEEELPPPP

Thank you kindly for expert guidance

 

[jorismoerings]

Hi,

As showing a time format is just what it says it is, a format, the trick of doing this right is, do ALL calculations with any of time related cells and only then format them to a time.
so i would try this:

Format((txtDepTm * 24 + Application.WorksheetFunction.VLookup(cboDep, Range("CD9:CE149"), 2, False)) / 24, "hh:mm")

 

[Av8tordude]

ok....I understand, however, txtDep is in a time format, but the vlookup cell is a number. Also, the code errors when I tried it.

 

[Av8tordude]

Format(Application.WorksheetFunction.VLookup(cboDep, Range("CD9:CE149"), 2, False) / 24, "hh:mm")

this code shows correctly [i.e. 5.5 (05:30)], however it does not display correctly if the number is a negative [i.e. -5.5 to (- 05:30)]

 

[jorismoerings]

Excel uses the 1900 date/time system and within can't show negative times.
The trick is to convert everything to a decimal number, do your calculation and only then apply any formatting.

So it's no issue the VLOOKUP delivers a decimal number. I assumed the txtdepTM was already a number, perhaps it wasn't and needs to be converted to a decimal number.

The the code should be:

1. (TimeValue(txtDepTm) * 24)- converts the timevalue to a decimal number
2. (Application.WorksheetFunction.VLookup(cboDep, Range("CD9:CE149"), 2, False)) - is a decimal number - no formatting necessary.
3. do your calculation
4. format the end result and not the steps in between;

Format(((TimeValue(txtDepTm) * 24)+(Application.WorksheetFunction.VLookup(cboDep, Range("CD9:CE149"), 2, False)))/ 24, "hh:mm")

 

[Av8tordude]

Thank you for the explanation. It makes sense now. Only one problem, if the time (i.e. 01:00) and the UTC time zone is -5, the results show 04:00. The result should be 19:00. How do I can I take into account for times that cross over the 24:00 mark?

 

[Av8tordude]

After taking apart each piece of the code, I manage to solve my problem (at least I haven't notice anything wrong)...

Format(((TimeValue(txtDepTm) * 24) + (Application.WorksheetFunction.VLookup(cboDep, Range("CD9:CE149"), 2, False)) + 24) / 24, "hh:mm")

 

[Av8tordude]

My apologies for my rudeness. Thank you jorismoerings for your expertise. I greatly appreciated.

 

[jorismoerings]

My apologies for my rudeness. Thank you jorismoerings for your expertise. I greatly appreciated.

No problem. We got there in the end. Glad i could help.