MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Time Questions

Posted by david m on February 11, 2002 7:41 AM

2 questions related to time:
1. Is there a way to format a cell so that a data entry operator only enters numbers (no colons) in a cell, and Excel returns a military time based on those numbers? For example, I'd like to enter 740 and have Excel return 07:40 or enter 1345 and have Excel return 13:45.

2. Is there a formula that can calculate in minutes the difference between two cells that contain time values? For example, if A1 is 13:30 and B1 is 16:45, is there a formula for C1 that would return 75 minutes?

Posted by Aladin Akyurek on February 11, 2002 7:50 AM

Q2: =(B1+(B1<A1)-A1)*1440 [NT]

Posted by Juan Pablo G. on February 11, 2002 8:05 AM

Aladin's answer was: Q2: =(B1+(B1 < A1)-A1)*1440 [NT]

Posted by Aladin Akyurek on February 11, 2002 8:06 AM

Again that less than thing...

Answer to Q2: =(B1+(B1 &LT; A1)-A1)*1440