# writing numbers as simple hh:mm but without AM/PM

1. ## writing numbers as simple hh:mm but without AM/PM

I'm creating a simple timesheet, where my team can enter simple values of time as Hours and minutes and no AM/PM involved.

So here is my problem, when i'm formatting any cell into time it's converting the value either to hh:mm am/pm or 1200 hrs format. But i don't want that value to be converted to any particular time of a day.

I want is simple hours:minutes format, where values can be greater than 24 hrs.

Any help would be highly appreciated .

2. ## Re: writing numbers as simple hh:mm but without AM/PM

Maybe custom format of

[h]:mm

3. ## Re: writing numbers as simple hh:mm but without AM/PM

i tried but did not work for me.

4. ## Re: writing numbers as simple hh:mm but without AM/PM

How are you entering the times?

Type some times into the forum, exactly as you want to type them into excel (Not how you want them formatted).

5. ## Re: writing numbers as simple hh:mm but without AM/PM

4:30 is 4 hours and 30 minutes not 4:30 AM.

or

40:20 should mean 40 hours 20 minutes.

Also, the calculation i want is
A1 = 20:50 (20 hours 50 minutes), B1 = 9:10 (9 hours 10 minutes), C1 (A1-B1) and should give the result as 11:40 (11 hours 40 minutes)

6. ## Re: writing numbers as simple hh:mm but without AM/PM

The format that I suggested is correct for the scenario that you describe.

Are you entering the times into excel in time format, or in numeric format?

7. ## Re: writing numbers as simple hh:mm but without AM/PM

i'm entering values as 30:20.

8. ## Re: writing numbers as simple hh:mm but without AM/PM

Then it should work.

How does the custom format that I suggested differ from what is expected?

9. ## Re: writing numbers as simple hh:mm but without AM/PM

okay, the moment A1 > 24 problem starts

A1 = 50:50 (50 hours 50 minutes), B1 = 10:40 (10 hours 40 minutes), C1 (A1-B1) and should give the result as 40:10 (40 hours 10 minutes)

but it's showing 16:10

10. ## Re: writing numbers as simple hh:mm but without AM/PM

Then you haven't applied the custom format to C1.

11. ## Re: writing numbers as simple hh:mm but without AM/PM

Worked ! THANKS. One last thing, what if A1<B1 ? its showing #########

12. ## Re: writing numbers as simple hh:mm but without AM/PM

Excel cannot display negative times , that is why you see ######

If you need to display times as negative then there is a workaround. Do you know how to apply conditional formatting?

13. ## Re: writing numbers as simple hh:mm but without AM/PM

No. I'm new to this. Can you share the steps how to do that?

14. ## Re: writing numbers as simple hh:mm but without AM/PM

Ok, First of all, you need to change the formula in C1 to

=ABS(A1-B1)

This turns the negative value into a positive value so that it displays correctly.

Next select column C (the entire column, by clicking the column letter at the top).

Go to the 'Home' tab on the ribbon, then click 'Conditional Formatting' followed by 'New Rule'

In the top part of the pop-up box, click 'Use a formula to determine which cells to format'

You should then see another box labelled 'Format values where this formula is true:' in this box, enter the formula =\$A1<\$B1 (don't press enter).

Click the 'format' button, then click the 'Number' tab on the new pop-up window.

Click 'Custom'

In the box under 'Type' delete the existing content, then enter the custom format "-"[h]:mm

Click the 'OK' buttons until you get back to excel.

Hopefully I haven't missed a step anywhere.

15. ## Re: writing numbers as simple hh:mm but without AM/PM

Originally Posted by jason.b75
Excel cannot display negative times
It can if you use the 1904 date system.

However, if you don't know what you're doing I would avoid this!

16. ## Re: writing numbers as simple hh:mm but without AM/PM

Useful to know, Tony.

18. ## Re: writing numbers as simple hh:mm but without AM/PM

Personally, I've not had the need to consider the use of the 1904 system, but I have encountered the problems it can cause in several past threads.

Think I will continue to avoid it.

There are currently 1 users browsing this thread. (0 members and 1 guests)