Thread: Time formats

I'm not sure I follow... Can you uplad example workbook with few inputs and desired outputs?

@tarsuc, if the values are as you say they are then:

=MOD(B1-A1,1)
format as h:mm

should work without issue (even with the likes of 11 am etc...)

If you get errors with the above apply TRIM to B1 & A1 ... failing that post a sample file.

I think I understand what you want to do....

You would prefer to enter times (hours and minutes) with a decimal separator, instead of a colon.

So 15.45 would mean 3:45 PM

If that's true, try something like this:
A1: start time....eg 11 (meaning 11 AM)
B1: end time.....eg 15.45 (meaning 3:45 PM)

This formula calculates the difference, in hours and minutes, between those 2 times

C1: =MOD(DOLLARDE(B1,60)-DOLLARDE(A1,60),24)/24

Format that cell as time (hrs:mm)

In the above example, the formula returns: 4:45

Here are some other examples:

Start       End         Elapsed
 9.00       11.00       2:00
23.00        2.30       3:30
16.00       19.25       3:25

Is that something you can work with?

Ah, good spot Ron - I missed that completely

On that basis - another alternative would be:

=MOD(SUBSTITUTE(TEXT(B1,"0.00"),".",":")-SUBSTITUTE(TEXT(A1,"0.00"),".",":"),1)

useful perhaps should you wish to use pre XL2007 and avoid ATP dependency.