0,5 time format is 00:00 and not 12:00

[Acroach]

Hi Guys,

I came across a weird thing in VBA for Excel:

For our planning department I designed a sheet so that the expected delivery times automatically get uploaded to our database. The times get rounded down to the nearest half our and than I add two hours and generate a string to upload in the database. All works fine except for times between: 12:00 and 12:29 in the code below info2 becomes the value "0,5" (or half day/ 12:00)

info3 = ActiveCell.Value
info2 = Application.WorksheetFunction.Floor(info3, 1 / (24 * 2))
info1 = info2 + 0.08333333333
info4 = "Verwachte lostijd tussen " & Format(info2, "hh:mm") & " en " & Format(info1, "hh:mm") & " SJAB "

The code above should come back with
"Verwachte lostijd tussen 12:00 en 14:00 SJAB"

but instead it gives me
"Verwachte lostijd tussen 00:00 en 14:00 SJAB"

I assume it has something to do with the formatting but i'm not quite sure.

 

[Fowmy]

Instead of "hh:mm", enter "hh:mm am/pm" in both places

 

[Acroach]

Thanks for your answer, but...

then I will get the AM/PM format while we prefer to use the 24h format.

 

[Fowmy]

What is time format in ActiveCell.Value?

 

[Acroach]

12:01 (h:mm)

 

[Andrew Poulsom]

I managed to reproduce your problem once, but now I get:

Verwachte lostijd tussen 12:00 en 14:00 SJAB

How have you declared your variables?

 

[Fowmy]

I too get the result as "Verwachte lostijd tussen 12:00 en 14:00 SJAB" when my activecell value =12:01 as Andrew.

 

[Acroach]

Ahhh.. that's it!

I declared all the info's as Strings, Now i've changed info2 and info3 to variants and it all works smooth!

Thank you very much for your help!

Kind regards from Holland!