Date Format

[jaycee]

Sorry if this has been requested before but I could not find the answer.

in cell B6 i want to add the time ie 0837 (hhmm format)
in cell C6 I want to add another time ie 0956 (hhmm format)
In cell D6 I need the difference of C6/B6 in minutes.

Whenever I try I get conflicting answers.

Many thanks

 

[JD_GFI]

Hi there,

Simplest solution is to have D6 set as "=C6-B6" and format it with a custom format of "[m]" (do not include "" marks).

 

[jaycee]

Thanks for that, but I get the problem in the first 2 cells when adding the time of 0856 it defaults to 0000 with hhmm format.

Any ideas on this one.

Many thanks

 

[JD_GFI]

Are you entering times as 0856 or 08:56?

 

[jaycee]

0856, that is wher the fault is, I want to set it up so that I do not have to add the colon

 

[JD_GFI]

I can't see a solution that would keep it as just three columns without the colon being entered. If you enter the time as a different format then excel will treat it as a decimal. eg 0837 to 0937 would return 100 minutes instead of 60. It can be done using additional helper columns though if that was acceptable?

Maybe someone else know more than I do however.

 

[jaycee]

OK, many thanks for the help