Hours in excel not calculating right 2

[Maisie2007]

Hi,

I got this formula for logging work hours online, but it's not calculating correctly when I only type in my beginning hours at the start of the day.

In the example attached I start at 6:00 am, but the total is tabulating 18 hours for the day when it should not be calculating anything yet.

Also, if I only work half a day, it doesn't seem to calculate correctly either. (I had to type into E4 and F4 yesterday for it to calculate correctly).

You will see what I mean when you view the jpg attached.
The formula for G5 is =(F5-C5+(F5<C5))*24
Anyone have any idea how to correct this?
Thanks!

 

[AnotherHiggins]

->The formula for G5 is =(F5-C5+(F5<C5))*24

It is important to understand this bit:
[tab](F5<C5)
If F5 < C5, it returns TRUE
If F5 > C5, it returns FALSE

The important part is that [!]TRUE = 1[/!] and [!]FALSE = 0[/!].

In Excel 1 is one day, or 24 hours. (See faq68-5827 for more information on how Excel deals with dates and times).

So, because your end time cell is empty, (F5<C5) returns true, which equals 1, which means that you are subtracting your start time from 24:00.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[buitenhek]

I am not as smart as "anotherhiggins" so I brute forced the formulas. I too have time problems so I convert the times to general numbers.

The formula is at each column bottom

In Out SubTotal In Out SubTotal Hrs Worked
8:00 12:00 4:00 14:00 15:00 1:00 5:00
8:30 9:30 1:00 10:20 14:30 4:10 5:10
7:00 7:51 0:51 7:52 7:53 0:01 0:52
6:00 8:00 2:00 2:00
7:00 15:00 8:00 8:00

IF(C7="","",C7) IF(D7="","",D7) IF(OR(H7="",I7=""),"",SUM(I7-H7)) IF(E7="","",E7) IF(F7="","",F7) IF(AND(K7="",L7=""),"",IF(AND(K7="",L7>=0),SUM(L7,-H7),SUM(L7-K7))) IF(AND(J7="",M7=""),"",SUM(J7,M7))


Regards
Peter Buitenhek
ProfitDeveloper.com

 

[AnotherHiggins]

Hmm. I suppose I didn't offer a way to fix the problem, I just explained why Excel was doing what it was doing.

I'd change your formula in G5 to
[tab]=IF(ISBLANK(F5), "", (F5-C5+(F5<C5))*24)

As there are many ways to skin a cat, either of the following would also work:
[tab]=IF(F5="", "", (F5-C5+(F5<C5))*24)
[tab]=IF(VALUE(F5)=0, "", (F5-C5+(F5<C5))*24)

Also - in case you weren't aware - the reason for adding 1 (1 day / 24 hours) when your end time is less than your start time is to deal with situations where an employee logs overnight hours. Even this solution will fail if someone ever reports time over 24 hours - but it seems unlikely that anyone would should do that.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Maisie2007]

That worked wonderful, thanks!