VBA Code for Time Format as HH:MM AM/PM

[OilEconomist]

Thanks in advance for your assistance. I would like to format a cell in VBA with the time format. I wrote the code, but that doesn't work. Just something to start.

Some point outs.
Hours show up with two digits, but I do not want military time.
If I do not enter AM or PM, it defaults to AM automatically.

Example 1:
Entry: 4 AM
Result: 04:00 AM EST

Example 2:
Entry: 4 PM
Result: 04:00 PM EST

Example 3:
Entry: 4
Result: 04:00 AM EST

Sub TimeFormatter()

For i = 1 to 100
Range("A" & i).NumberFormat = "HH:MM AM/PM EST"

End Sub

 

[Micron]

Those results are desired results, not what you get?
If you only enter a single digit number (e.g. 4), you will get the 4th day of January 1900 at 12:00 AM if the cell is formatted as a number (or so I think).
If you want 4 and not 04, then alter the HH to H
I don't believe EST is a valid parameter for date/time formats but happy to be proved wrong. Do you have a reference that states otherwise?
If your cell is formatted as text I don't believe that code will have any effect.
Possibly you could use Time function if you can provide hour, minute and second values somehow, but you'd have to pass 13 to it to get 1:00 PM.

 

[OilEconomist]

Those results are desired results, not what you get?
If you only enter a single digit number (e.g. 4), you will get the 4th day of January 1900 at 12:00 AM if the cell is formatted as a number (or so I think).
If you want 4 and not 04, then alter the HH to H
I don't believe EST is a valid parameter for date/time formats but happy to be proved wrong. Do you have a reference that states otherwise?
If your cell is formatted as text I don't believe that code will have any effect.
Possibly you could use Time function if you can provide hour, minute and second values somehow, but you'd have to pass 13 to it to get 1:00 PM.

Those are desired results.

I understand right now if I enter a single digit, I may get will get the 4th day of January 1900 at 12:00 AM.

No I do not have a reference that states EST is a valid parameter for date/time.

Passing 13 to get 01:00 PM EST is fine.

I don't know if there is a VBA code that is able to do what I would like, but that's why I posted here.

 

[Dave Patton]

The attached shows function examples in 2 cells; the cells as custom formatted.

You can use VBA.

 

[OilEconomist]

The attached shows function examples in 2 cells; the cells as custom formatted.

You can use VBA.

Thanks @Dave Patton . I do not see any attachments.

 

[Dave Patton]

reposted
The attached shows function examples in 2 cells; the cells are custom formatted.
You can use Excel's macros recorder as you enter the information and then convert the relevant parts to your VBA.


You can use VBA.

Date and Time_21.xlsm
	A	B	C
1
2	04:00 AM EST	custom formatted hh:mm AM/PM "EST"
3	05:00 PM EST
6c

Cell Formulas
Range	Formula
A2	A2	=TIME(4,0,0)

 

[OilEconomist]

reposted
The attached shows function examples in 2 cells; the cells are custom formatted.
You can use Excel's macros recorder as you enter the information and then convert the relevant parts to your VBA.


You can use VBA.

Date and Time_21.xlsm
	A	B	C
1
2	04:00 AM EST	custom formatted hh:mm AM/PM "EST"
3	05:00 PM EST
6c

Cell Formulas
Range	Formula
A2	A2	=TIME(4,0,0)

Thanks @Dave Patton , do I need to download XL2BB to obtain the code?

 

[Dave Patton]

Thanks @Dave Patton , do I need to download XL2BB to obtain the code?

I did not include any code.
I showed an example entering time with an Excel function time and entering the time in the cell 17:00:00'

I then custom formatted the 2 cells. You can paste my post into a clean sheet.

Click on the icon below the f(x) in the heading of the post example and then move to your sheet and paste.

 

[OilEconomist]

Thanks @Dave Patton , do I need to download XL2BB to obtain the code?

I did not include any code.
I showed an example entering time with an Excel function time and entering the time in the cell 17:00:00'

I then custom formatted the 2 cells. You can paste my post into a clean sheet.

Click on the icon below the f(x) in the heading of the post example and then move to your sheet and paste.

Thanks @Dave Patton, I came up with the following. I just need to see if I can trim it down to one line.


Sub Test

Macro2()

For i = 6 To 11
    Range("D" & i) = "=TIME(B" & i & ", 0, 0)"
    Range("D" & i).NumberFormat = "hh:mm AM/PM ""EST"""
    Range("D" & i).Formula = Range("D" & i).Value
Next i

End Sub