I've autogenerated time from an API in the format "15-03-2022 01:10", here I would like to take the timepart and convert it to 13:10 and so on. As there is no AM/PM at the end of time, it is getting difficult to apply the Excel time format, is there any way I can achieve this using excel VBA.

I Searched everywhere, but couldn't find the correct solution for the above issue. Some example like 11:30 --> 11:30 Morning 11:30 --> 23:30 Night just trying to figure out how to distinguish between the above 2 formats in a single day. But the end output needs to be in 24hr format.

CodePudding user response：

Always handle date and time as Date, not text, no exceptions. So:

NewTime = DateAdd("h", 12, CDate("15-03-2022 01:10"))

Then apply the format you wish for display, here:

dd-mm-yyyy hh:nn

CodePudding user response：

Every day in Excel is 1, so half a day is 0.5.

CDate("15-03-2022 01:10") 0.5 to add half a day.

Also you can concatenate PM to the end of the string

CDate("15-03-2022 01:10 PM")

Then you can output any display text you want with the Format function.

Format(MyTime, "MMM dd, hh:mm:ss am/pm") would display "Mar 15, 01:10:00 PM"

For 24 Hour time formatting, remove the am/pm:

Format(MyTime, "MMM dd, hh:mm:ss") would display "Mar 15, 13:10:00"