Hi guys. I was hoping someone might have an easy solution for turning:
0722 in a cell (representing 7hours 22 minutes)
into
7 H 22 M
where anytime 4 numbers are typed into N2:N1000, it will auto convert it to # H # M.
Another example: I type 0815 into cell n2 and hit enter. It converts to 8 H 15 M.
Thanks.
Use this format
#h##\m
Not sure how that answers my question. I'm looking for vba code. The format 7 H 30 M I'm using must stay the way it is as many other formulas depend on it displaying that way.
Selection.NumberFormat = "#\h##\m"
or
Range("A1").NumberFormat = "#\h##\m"
theres just an extra backslash in vba numberformat
You want format right? Why not the standard Excel format?
because I don't like that format. Also, because I have too many formulas that tie into the # H # M format I'm using. The format I chose is easier to read at a glance. Since I'm using my workbook on a tablet, I don't want to have to type H and M. I just want to type a 4 digit number and have vba correct it for me.
This is what Jec is saying
Option Explicit Sub Whathemeant() Dim formatcel As Range Application.ScreenUpdating = False For Each formatcel In Range("n2", Range("n" & Rows.Count).End(xlUp)) formatcel = Format(formatcel, "#H ## \M") Next Application.ScreenUpdating = True End Sub
Last edited by AB33; 01-14-2022 at 04:47 PM.
I tried pasting your vba into the sheet and it doesn't do anything. I typed in 0730 into a cell and it just displayed as 730
im not sure i follow? is it the entiresheet you need to format this way? in that case you can just use cells.numberformat = "#\h##\m"
Edit: I just read it was the range N2:N1000
in jthat case
Range("N2:N1000").numberformat = "#\h##\m"
Last edited by Crib; 01-14-2022 at 05:08 PM.
@saeber, you obviously don’t know what formatting does. You don’t need to type H and M with these examples.
Exactly what you are asking
Vba and tablets? Bad idea
what's wrong with vba and tablets?
Paste the code in to the module (Not sheet) and make sure that you have data from N2 to the last active cell in column N.
AB33, I created a module and pasted it in. Still doesn't do anything.
A lot, depending on which tablet. Microsoft tablets will do.
Maybe it would be easier to attach my workbook? I just tried but when I click on the paperclip icon, it won't do anything
Again, with standard formatting....
Just Click reply, then in the next page, there is an attachment (Please red the banner at the bottom)
refer to mileage and pay tab in column n. I'm also having trouble in column s where when I add text to the formula in the cell return it displays the date and time as a decimal. I'm not sure what the formula is to restructure the decimal back into a date format of mm/dd/ 13:30.
Last edited by Saeber4777; 01-14-2022 at 05:29 PM.
Already tried my file?
The code only works with active sheet.
It works with sheet Mileage & Pay
what is issue then?
Ive changed the format in column N (Right click, Format Cells..., Custom, #\H##\M)
and ive insertet the Text(Value, "mm/dd/yyyy hh:mm") to the formula in column S.
Hope this solves your issues.
Thanks Crib! Works great. Is there a way to make it so that the format 7H20M displays as 7 H 20 M?
"#h ## \m"
I put that in and it doesn't change the format to 7 H 20 M from 7H20M. It's the same code that was in there before. How do I add the spaces?
thank you AB33
If I can't add the spaces, my formulas will return an error message because it is trying to read the H and M as numbers.
Well, just tried out and it works.
I have two codes as the name indicate.
Run the first code on any active sheet as long as your cursor is on that sheet when you run the code.
Sub Activeshhetonly() Dim formatcel As Range Application.ScreenUpdating = False For Each formatcel In Range("n2", Range("n" & Rows.Count).End(xlUp)) formatcel = Format(formatcel, "#H ## \M") Next Application.ScreenUpdating = True End Sub 'this code only works with sheet Mileage & Pay Sub Mileagesheetonly() Dim formatcel As Range Application.ScreenUpdating = False with sheets("Mileage & Pay") For Each formatcel In .Range("n2", .Range("n" & .Rows.Count).End(xlUp)) formatcel = Format(formatcel, "#H ## \M") Next Application.ScreenUpdating = True end with End Sub
Last edited by AB33; 01-14-2022 at 06:40 PM.
I can get it to work when I manually run the vba code. When I type it into the cell, it doesn't autocorrect until I manually calc.
by manually calc I mean hit the play button in vba window
Put this code on sheet
When you add or change any data in column N, the code is auto triggered. The code does not go into a module, rather in a worksheet as attached.
works. Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks