# How can I (in effect) subtract Time in Excel when the answer will be greater than 24 hours?

I'm trying to write an Excel sheet which calculates Flexi-time hours remaining to be worked in a week.

The user may enter the time they started work and the time they ended work, and the spreadsheet will calculate how many hours they worked that day. This is (obviously) a simple formula, `=C4-B4`.

However, if I try to take this same approach to calculate the hours remaining to be worked in a week (i.e. 37 - 11, or, more accurately, `=TIME(37,0,0) - TIME(11,0,0)`) then it does not work, because the time is automatically converted to 24 hour time, so the answer, 26, would in fact become 2.

Is there a way to work with hours which gets around this issue whilst preserving the precision of times?

I have seen similar spreadsheets made where the user may simply calculate hours worked in decimal (i.e. 18.5 for 18 and a half hours) however I would like to preserve the accuracy a TIME format can provide.

Is there any way around this?

-
What does your spreadsheet look like right now? I assume you have `Time In | Time Out | Total Time` and `Total Time` is `=(C4-B4`)*24`. With that, can't you just `=37-<Total Time>`? – shinjijai May 2 '14 at 14:26
As I said, `Total Time` is simply `=C4-B4` - why would it be `=(C4-B4)*24`? – Eilidh May 2 '14 at 14:34
See below answer. Internally, time is a stored as decimal days (and can be greater than 1). Just multiply by 24 to convert to hours. – Madball73 May 2 '14 at 14:37

You'll have to convert to decimal, but you can keep having the users enter as time...

``````=37-D4*24
``````

Behind the scenes, your time is just a decimal (of a portion of a day), so by multiplying by 24 you will calculate the number of hours...

EDIT: If you want to simulate "Time" formatting, you can use this (note, it's no longer a number, so can't be used in downstream calculations):

``````=TRUNC(37-D4*24)&":"&TEXT(ROUND(MOD(37-D4*24,1)*60,2),"00")
``````
-
What is `A1:A3` exactly? The hours worked is `C4-B4` (where `C4` is `=TIME(17,0,0)` and `B4` is `=TIME(09,0,0), for example. This would give an answer of `08:00` (in, lets say, `D4`) If I try `=37-VALUE(D4)*24` it does not work, and gives me 12:00 as an answer, when it should be giving an answer of 29:00 in that case. – Eilidh May 2 '14 at 14:39
Answer was written before cell addresses or layout was provided. Time is Time of Day, not hours. Whatever cell =37-VALUE(D4)*24 is in should be formatted as a decimal--you will see your 12:00 change to 29.0. Oh, and VALUE() is uncessary. I will update. – Madball73 May 2 '14 at 14:44
This has the problem of losing accuracy. i.e. `37h - 6:08h` should become `30:52h`however it shows `30:87` which is confusing to the user. Any ideas for a workaround? – Eilidh May 2 '14 at 14:50
`30.87` is 30 hours and 52 minutes (rounded). – shinjijai May 2 '14 at 14:55
Yes. I know. I did not say it was incorrect, simply that is confusing to the user. For example, were the result `30.25` the user may think it was `30h 25m`when in fact it is `30h 15m` – Eilidh May 2 '14 at 15:26