1

Is there a formula for Is there a formula for calculating the difference in minutes or hours between two times in two columns?

In my spreadsheet I have the following two columns called start time and end time. In those fields I enter the time in hours and minutes:

enter image description here

start   end
6:45    7:45

I'd like a third column that has the total difference between them like so:

enter image description here

Is there a formula that will do this? This is for Google Sheets but IIUC formulas from Excel may work in Google sheets???

UPDATE:
Using the formula suggested, =(B1-C1), it's giving me the correct hours and minutes (edit - actually it does work see update 2).

enter image description here

Using the other suggestion, =(B1-C1)*24, I'm not getting the following value:

enter image description here

In the second case, if I change the time field to a number it changes:

Before (formatted as automatic):
enter image description here

After (formatted as Number):
enter image description here

Here's the menu items:
enter image description here

UPDATE 2:
The formula does work. To get it to work I needed to format the formula cell as a number, not the time cells.

4
  • 3
    Be careful of daylight savings time changes, unless the formula is "smart" enough to take that into account, on a region-by-region basis. There's a good reason astronomers use Julian date: scienceworld.wolfram.com/astronomy/JulianDate.html Commented Nov 29, 2022 at 1:45
  • 1
    It generally 'just works' - c2 = b2 - a2
    – mcalex
    Commented Nov 29, 2022 at 2:02
  • Otherwise convert the time/date to Unix time and again the maths is pretty straight forward. It looks like you don't have date so that might not help.
    – Insanemal
    Commented Nov 29, 2022 at 5:22
  • @Insanemal thanks. i noticed when selecting some of the cells that Google Sheets auto detected or inferred that I was entering time information. i tested it and when I put AM or PM in the field or when I use a colon between the numbers but if it's more stable i'll select the time field for this column Commented Nov 29, 2022 at 19:29

1 Answer 1

1

Try this method:

  1. Format cells to Number enter image description here

  2. Enter the formula:

    =(B1-A1)*24

enter image description here

4
  • That works!!! :) Commented Nov 29, 2022 at 19:20
  • The formula executes on cells b1 and a1. Is it possible to make it apply to all the rows? So column C executes the function on the current row of column B and A? Commented Nov 29, 2022 at 19:24
  • superuser.com/questions/1755306/… Commented Nov 29, 2022 at 22:52
  • 1
    array formula: ctrl+shift+enter
    – Lee
    Commented Nov 30, 2022 at 7:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .