Date and time in two columns respectively. How to obtain difference in hours?

0
votes

I have a large spreadsheet in which a start date is captured in cell D10 and a start time in E10. The end date is captured in J10 and end time in K10. In column y I need the difference in hours such that I may total column y to give me the total in hours that the generator was utilised in a month.

I have tried concatenating then using a formula like :- =TEXT(x10-w10, "d:h:mm")

1
excel
Why are you using W10 and Y10 in your formula when the information is in cells D10, E10, J10 and K10? – Jerry
@Jerry I concatenated D10&E10 in W10 and J10&K10 in x10 then used the formula =TEXT(x10-w10,"d:h:mm") – Anthony
If you store dates and times as actual dates, (j10 + k10) - (d10 + e10). The result is also a date to which you would apply the [h]:mm format to make sure h can go beyond 24. If you store them as something else, store them as dates instead. – GSerg

1 Answers

1
votes

Try

=(J10+K10-D10-E10)*24

Then change format to number.

In Excel, a day difference is equal to 1, therefore an hour difference is equal to 1/24.