Add 20 minutes to a time- end result has to be left as text format

[Livin404]

Good evening, I need to add one more time to my spread sheet. In Column D I have a time (24 hour clock) in a text format. This is so I can have numbers beginning with 0 and have no colons. Is it possible to come up with a VBA that will add 20 minutes to numbers that are listed in Column D and place the end result in Column E. I provided an image of a before and after short of worksheet "72 Hr". Naturally I'll need both Columns D and E to end up as a Text. Thank you,

 

[jasonb75]

This is so I can have numbers beginning with 0 and have no colons.

Any reason why you can't just use normal time with a custom format of hhmm (no colon)?

 

[Livin404]

Any reason why you can't just use normal time with a custom format of hhmm (no colon)?

If that will work, I don't know if that involves changing the other into a custom format. It took up to three weeks to get me this far, and this is the final macro. There are about 40 macros in total and about four of them involve the time. Having said that I thought maybe there was something that will change column D very briefly to a custom format than update Column E then change everything back to text. So much work has went into this, I don't want to mess anything up for it works perfect now. I just want to add that time. It is a must the 0s are visible on times before 1000. Thank you,

 

[Livin404]

Any reason why you can't just use normal time with a custom format of hhmm (no colon)?

Hello I have the time format in a custom at this point, see photo. I would then need to add 20 minutes and have that result in Column E. Then I I can use the formula below but I'll need to include E in addition to Column D. I think this will work. Thank you,

Sub twenty_four_hour_Clock()
 With Range("D1", Range("D" & Rows.Count).End(xlUp))
.NumberFormat = "@"
.Value = Evaluate(Replace("if(@="""","""",if(isnumber(@),text(mod(@,1),""hhmm""),@))", "@", .Address))
End With
End Sub

 

[Livin404]

Any reason why you can't just use normal time with a custom format of hhmm (no colon)?

I know that

=D7+20/1440

does work, but I just need that translated to a VBA with the results posted in Column E, and only for those with a time in Column D.

 

[jasonb75]

If you want to add the 20 minutes to the code in post 4 then you should be able to do it with a small edit to the currently evaluated formula, mod(@+20/1440,1)