Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
3  
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 at 14:26
    
As I said, Total Time is simply =C4-B4 - why would it be =(C4-B4)*24? –  Eilidh May 2 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 at 14:37

1 Answer 1

up vote 1 down vote accepted

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")
share|improve this answer
    
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 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 at 14:44
    
This has the problem of losing accuracy. i.e. 37h - 6:08h should become 30:52hhowever it shows 30:87 which is confusing to the user. Any ideas for a workaround? –  Eilidh May 2 at 14:50
    
30.87 is 30 hours and 52 minutes (rounded). –  shinjijai May 2 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 25mwhen in fact it is 30h 15m –  Eilidh May 2 at 15:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.