Time / Hours Format

[nkitchen31]

The cell that has a value of "96:00" I actually entered "4"
The cell that has a value of "4:00" I actually entered "4:00"

How can I change the cell to automatically have it value of "4:00" if I only type in "4" ... the way it is setup now is you have to add the extra :00 which is annoying.

Thank you in advance!

[NickyC]

Excel is reading your entry of 4:00 as date/time not a value equal to 4.
To fix the numbers as 2 decimal places click on "format" in the Cells group and select format > cells > number and choose 2 decimal places

[nkitchen31]

Yea but that will still won't work because the total column is adding it as time - [hh]:mm. The point is to add hours in the entire column..No decimal places.

4:00
11:00
5:00

Total: 20:00 hours

I just don't want to have to add the ":00" to it every time.

Thank you again.

[CaptMac]

Then you have to format the cell the total is going into as Time.
Mac

[nkitchen31]

Here is a screenshot of the entire spreadsheet, maybe this helps...

Reference B6

Thank you.

[NickyC]

Hi
if you are entering numbers in a cell with time/date format you will have to enter either 4:00 or =4/24, because in this format " 1 " is a day (=24 hours)

why not just apply 2 decimal places to the whole column, including the total?

of, if you want the totals in hours format but the cells above in number format, divide your total by 24

=(sum(H5:H30)-B5-B7)/24

[bebo021999]

Format/custom cells H5:H30 with 00":00".
See attachment.

[nkitchen31]

.... see below ... filler ...

[nkitchen31]

Format/custom cells H5:H30 with 00":00".
See attachment.

That is good, except now when I enter "3:15" for example, it displays as a decimal lol

[bebo021999]

.... see below ... filler ...

I create a helper column. See attachment.

[nkitchen31]

I create a helper column. See attachment.

Thank you. I see what you are saying, but the point is to not have a formula where the user is going to enter data. Because for the following month, they are going to have to copy and paste the formula back through the "H" column again.

There's got to be another way...

[NickyC]

one solution may be to use a macro triggered by a worksheet change event so that any whole number greater than 1 inserted in your input range is automatically divided by 24. I am not very fond of these macros as they can cause unexpected oddities, but you might want to give it a go.

This macro assumes that you will be inserting your values in range H5:H30 and that this range is formatted as time. The macro will automatically divide any whole number of 1 or more entered in this range by 24, converting it to hours.

To add a macro to the worksheet, right click on the tab with the sheet's name and click "view code" then paste this code:

Please Login or Register  to view this content.

[nkitchen31]

one solution may be to use a macro triggered by a worksheet change event so that any whole number greater than 1 inserted in your input range is automatically divided by 24. I am not very fond of these macros as they can cause unexpected oddities, but you might want to give it a go.

This macro assumes that you will be inserting your values in range H5:H30 and that this range is formatted as time. The macro will automatically divide any whole number of 1 or more entered in this range by 24, converting it to hours.

To add a macro to the worksheet, right click on the tab with the sheet's name and click "view code" then paste this code:

Please Login or Register  to view this content.

I'm with you on that one about the Macros ... thank you for all your responses. That just sucks, I thought there would be an easier way. It all works EXCEPT on a whole number. 4:15, 6:45, 2:14, 9:28, etc... all work fine... frustrating haha

[Aardigspook]

The solution I use for entering times is Autocorrect, set up to replace ++ (plus plus) with : (colon) so when I'm entering times I just enter 4++15 and Excel converts that to 4:15. That lets me do it all on the number pad.
The problem with that method, of course, is that it only works for me - not for anyone else if they don't do the same.