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

[satbirarora]

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 .

Thanks in Advance!

[jason.b75]

Maybe custom format of

[h]:mm

[satbirarora]

i tried but did not work for me.

[jason.b75]

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).

[satbirarora]

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)

[jason.b75]

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?

[satbirarora]

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

[satbirarora]

i'm entering values as 30:20.

[jason.b75]

Then it should work.

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

[jason.b75]

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

[satbirarora]

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

[jason.b75]

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?

[Tony Valko]

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!

[satbirarora]

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

[jason.b75]

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.

[jason.b75]

Useful to know, Tony.

[Tony Valko]

See this...

Differences in the 1900 and 1904 date systems

[jason.b75]

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.