0

I can't figure out how to convert 12:00:00 AM to number/decimal. I tried to convert the Time Format to "Short Time" or "h.mm" but it didn't work and it still returns 0.

Dim dbConvertTime as Double
Dim dtTime as Date

dtTime = Format("12:00:00 AM","Short Time")
dbConvertTime = (dtTime * 24)

*Output dbConvertTime = 0

I wanted convert the Time(12:00:00) to 24:00:00 because this data(12:00:00) returns 0 when multiplied by 24. The output should be = 576.00

bigbryan
  • 411
  • 6
  • 19
  • 36
  • 3
    You might want to read through this to better understand dates / times in Excel: http://stackoverflow.com/a/37101358/1153513 This is probably also a good read: http://stackoverflow.com/a/38001028/1153513 Afterwards, you'll come to understand that **any** number with no decimal places (so, a zero after the decimal point) can be interpreted as a date / time and will always equal to `12:00:00 AM`. – Ralph May 08 '17 at 08:48

1 Answers1

0

Will return the hour of a timestamp

Hour(Now())

This will return a 24 hour format timestamp. If you want a 12 hour format you could do the following:

dtTime = IIf(Hour(tme) > 12 Or Hour(tme) = 0, Abs(Hour(tme) - 12), Hour(tme))

I've used abs here to return 12 for when time is 12 AM

Tom
  • 9,725
  • 3
  • 31
  • 48
  • With my best feelings - don't you think, that the most used spreadsheet solution worldwide would have something better than a custom formula for a trivial task like converting time? - https://msdn.microsoft.com/en-us/library/office/gg251755.aspx – Vityata May 08 '17 at 09:00
  • @Vityata There is more than one way to skin a cat. Also NumberFormat as the OP stated won't return 12 when the time is "12:00:00 AM". Where as the above will. Also this is hardly a custom formula when everything is standard functionality that is available in both vba and worksheet functions – Tom May 08 '17 at 09:19