• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

excel 2007 how to change cell format h:mm to h.mm

Via Excel 2007 under 'Format cell format > Custom' I want to use the 'period' (fullstop) instead of the colon. This is because to input a colon into a cell one needs to press 'Shift' then colon, whereas a 'period' (fullstop) doesn't require 'Shift', and thus allows for quicker & easier input.

If I cannot change this format code, then I figure I would need to remap my keyboard keys.

Your thoughts would be appreciated please.
0
kazmdav
Asked:
kazmdav
  • 6
  • 5
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

the cell format is for displaying the value, not for entering.

If you want to ENTER a time value with a different format, then you need to change your system settings for the time format. This will affect all applications on your system, though.

cheers, teylyn
0
 
kazmdavAuthor Commented:
Thank you Teylyn. I have now changed my system settings & will test things out later today.
0
 
jan24Commented:
Good solution, teylyn.  If you want to only affect Excel, then you could instead leave your time settings as they are, and instead use a macro that catches the WorsheetChange event and converts decimal values to times.  If that sounds like a useful solution then let me know and I'd happily write that macro for you.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
kazmday, jan24's suggestion would be good if you only need this behaviour in specific range of a spreadsheet.

I've recently written a VBA macro for a team who work under immense time pressure. They need to enter a date/time value and can't be slowed down by entering all the characters. So now they enter a number in 24 hr format and a + or - sign to indicate a day ahead or in the past, e.g.

15.00+ returns 3 pm tomorrow
7.00 returns 7 am today
0.30- returns 12:30 am yesterday

This only affects specific areas of the spreadsheet. In other cells, they can enter 15.30 as a number and it stays a number.

If that is something you are interested in, I'll dig it up.

cheers, teylyn
0
 
kazmdavAuthor Commented:
Thank you for both ideas. I had to confer with the end users, thus my delay.

Teylyn, your idea brought to light a requirement that they sometimes need to deal with, which is, calculating to / from number of hours (at a site visit) that spans across 1 day.

Therefore, I'd appreciate if you could dig up that macro please, because I think your macro will be a good start for me to work with.

I have a little VBA experience & will aim to modify it to calculate hours across a day and remove 'tomorrow' & 'today' text. If I need help I'll come back to the forum.

Regards, kazmdav
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

I'll dig up that macro, and modify it for publication, but in the meantime:

If you subtract one date/time value from another and the result is more than one day, then you can format the result with square brackets around the hour portion to show values greater than 24.

For example:

<Sept-13-2011 3pm> - <Sept-12-2011 8am>

The result is one day and 7 hours, or 31 hours. If that is formatted as a regular time value, you'd see only

7:00

If you format it with the custom format

[hh]:mm

it will be displayed as

31:00

The underlying value is the same, i.e. the number 1.291666667

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
See the attached file for the macro I mention above.  

The values are actually entered as 900 or 2230, i.e. without a decimal point.

Hope you can do something with that.

cheers, teylyn
Simplified-Date-Time-entry.xlsm
0
 
kazmdavAuthor Commented:
Thank you Teylyn. I'll work on it & respond with how I go. Much appreciate your responses.
0
 
kazmdavAuthor Commented:
Hi Teylyn, I've been delayed due to catching a bug.... as soon as I recover I will get onto this. I will keep this question updated.
0
 
kazmdavAuthor Commented:
I'll be back on deck & will work on this on Tuesday 11 Oct.  It was quite a nasty bug and I thank you for your patience.

Regards, Kazmdav
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Thanks for the heads up, Kazmdav. Glad to see you're back on deck.
0
 
kazmdavAuthor Commented:
Thank you. Your code worked like a charm Teylyn.

Apart from just changing the target range, I only needed to change the display format of the cells to just "h:mm AM/PM".  To do this I added one simple line of code in the 'Worksheet_Change' procedure; as indicated in attached code excerpt.

It might seem strange not to display the actual date in the cell, but its what the user wanted & he's happy!
' return target cell to general format
    Target.NumberFormat = "General"
    Target = TimeEntry(iTarget)
    Target.NumberFormat = "h:mm AM/PM"   < I added this line

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now