Format cell as minutes for use in time calculation?

Question

I have 4 columns: Time In, Lunch, Time Out and Daily Total. The Daily Total column uses the following formula:

=([Time Out]-[Time In])-[Lunch]

I want to be able to simply enter minutes for the Lunch field. What format does the cell need to be for the formula above to work?

I've tried [mm] but when I type "42" it changes it to 60480. I've tried a regular number, but then the formula fails. I've tried various time formats but then typing 42 changes it to 0:00 and the formula bar says '2/11/1900 12:00:00 AM'.

What do I need to do?

Answer 1

mm is correct to format for minutes, or [mm] if you want it to go over 60 as needed.

However, you need to enter your data in days, as all date/time in Excel is handled in days. So, 30 minutes are 1/2*1/24 day, which is about 0.020833. If you enter that, it will show '30'.

Now that might not be very convenient, but it is the only way to really work with minutes. Another option is to fake it, and format the cell as 0" min". that way, Excel considers it a normal number, but shows it with " min" behind the number, which makes every human read it as minutes.

The only disadvantage is if you want to do calculations with it, at that time, you need to remember that it is just a number and not really in minutes, so if you use it in formulas, you need to divide it by the factor between minutes and days (24*60) - or keep faking the unit with formats.

Answer 2

Format the Time in and Time out columns as Time and the Lunch and Daily Total columns as h:mm.

Enter the lunch minutes like this: 0:30. Excel will recognize that as minutes. Now the calculation will work.