Compute Time

[Vtookup]

Hi All.
I'm confused as how the sample video works and not mine. followed danny rocks and Michael
Girvin's works... but anyway, i'm computing time, end result will look like this (based on 8 hours work)
what will be the formula and format of Total and overtime?

in out in out [work hours] [overtime]
9:00Am 11am 5:00Pm 12:00Am 9 1
8:00Am 12pm 2:00Pm 6:00Pm 8 0

thanks in advance for the help

 

[admiral100]

Hi,

One way to do that : Remove space from formula it's an html error

E2 <a2))+(d2-c2+(d2<c2))),"h:mm")
<a2))+(d2-c2+(d2<c2))),"h:mm")
=TEXT(((B2-A2+(B2 < A2))+(D2-C2+(D2 < C2))),"h:mm")

F2 =TEXT(E2-(8 / 1440)*60,"h:mm")

	A	B	C	D	E	F
1	In	Out	In	Out	Work hours	Overtime
2	9:20 AM	11:25 AM	5:00 PM	12:00 AM	9:05	1:05
3	8:20 AM	12:45 PM	2:00 PM	6:00 PM	8:25	0:25

<tbody>
</tbody>

</a2))+(d2-c2+(d2<c2))),"h:mm")></a2))+(d2-c2+(d2<c2))),"h:mm")

 

[JumpingCrab]

A possible way to do this:

Change the format for the time entry cells to [h]:mm (type in when in Format Cells>Custom). That will allow people to fill the cells using 24 hour format: 9:00 to 11:00 and 17:00 to 24:00.
Assuming the first series is in cells A2:D2
The total hours is calculated in E2 as

=((B2-A2)+(D2-C2))*24

The OT is calculated in F2 as:

=E2-8

You have to use 24 hour time when using this method, otherwise you have an issue if you go past midnight. Any time out past midnight has to be entered as 24:50 or 25:30, or something like that.
You can use standard time notation (non-24), but then you have to do some math when the out time is past midnight (you have to add 24 hours back in)

Hope this helps.

JL

 

[Vtookup]

Wow. TEXT function, i underestimated it. the word itself have you think that it really have nothing to do with numbers.
i'm wrong and it just answered my frustration. Thanks admiral100.
also thanks to jumpingCrab, the 24 hour clock is not popular in my work place. also it's common on-line solution,
which is hard to process inside my head. for me, admiral100 solution is what i wanted. straightforward. no need for MOD, formating etc. just one convertion, if i need computation.
Thanks again guys for the help. God Bless.

 

[14thgerald]

Hi, Good day everyone.
I'm having a problem figuring out how to to this : If A2<=1:30Am, Then B2=2:00AM, If A2<=2:30AM, Then B2=3:00 Am.

If you entered for example 12:00 - 1:30 on cell A2, i want the cell b2 to display 2:00
Thanks in advance.