Trying to designate time frames

A

alanMAC

Board Regular

Joined

Feb 11, 2005

Messages

77

• Mar 10, 2005

• #2

maxxnic,

You can use the =HOUR & = MINUTE functions to check the times.

i.e. If the start time is 21:00 in cell B3 then =HOUR(B3) will return 21

If the start time is 21:30 in cell B3 then =MINUTE(B3) will return 30

You can then use this information to calculate if the time is after 22 and before 6 (hours) time and the minutes accordingly.

Post some actual data and I will have a further look.

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Mar 10, 2005

• #3

Hi,

Try:

C2: =B2-A2+(B2< A2)

D2: =SUMPRODUCT((MOD(ROW(INDIRECT(A2*1440+1&":"&(B2+(B2< A2))*1440))/1440,1)< --"06:01")+(MOD(ROW(INDIRECT(A2*1440+1&":"&(B2+(B2< A2))*1440))/1440,1)>--"22:00"))/1440

format cells as [h]mm

Book1
	A	B	C	D
1	Start time	End time	Total time	time between 22 - 06
2	12:00	23:00	11:00	1:00
3	19:00	07:00	12:00	8:00
4	01:00	15:00	14:00	5:00
5	15:30	00:35	09:05	2:35
Sheet3

 

Upvote 0

M

maxxnic

New Member

Joined

Mar 10, 2005

Messages

4

• Mar 10, 2005

• #4

All_Employees.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		10/03/2005			Reg.Hrs	8	OvertimeHrs		1,5
3
4	ID	Name	StartTime	EndTime			EndTimeoff	StartTimeoff	TotalHrs	WorkedHrs	Reg.Hrs	OvertimeHrs	/Hrs	Reg.()	Overtime()	Total
5	204		08:30	16:30					8	8	8	0	3,56	28,44	0,00	28,44
6	203		09:00	18:00					9	9	8	1	3,16	25,28	4,74	30,02
7	497		17:00	23:00					6	6	6	0	4,17	25,01	0,00	25,01
8	1		08:00	16:00					8	8	8	0	4,02	32,15	0,00	32,15
9		------------							0	0	0	0	----	---	----	----
10		------------							0	0	0	0	----	---	----	----
Εργατες

hope this will help you Alan.

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Mar 10, 2005

• #5

My tryout
kind regards,
Erik

EDIT
formulas don't seem to be displayed

E7 : =IF(C7< night_start,24-night_start,24-C7)
F7 : =IF(D7< =night_end,D7,night_end)
G7 : =E7+F7
H7 : =IF(C7< night_start,24-night_start,24-C7)+IF(D7<= night_end,D7,night_end)

working time night.xls
	C	D	E	F	G	H
2			night_start	night_end
3			22:00	6:00
4
5	worked		midnight
6	from	to	before	after	sum	within single formula
7	23:15	7:00	0:45	6:00	6:45	6:45
8	19:00	7:00	2:00	6:00	8:00	8:00
9	20:55	4:30	2:00	4:30	6:30	6:30
10	22:47	5:44	1:13	5:44	6:57	6:57
11	22:00	5:30	2:00	5:30	7:30	7:30
12	23:00	5:30	1:00	5:30	6:30	6:30
13	0:00	5:30	0:00	5:30	5:30	5:30
Blad1

 

Upvote 0

M

maxxnic

New Member

Joined

Mar 10, 2005

Messages

4

• Mar 10, 2005

• #6

Thanks alot guys,

Fairwinds, seems like your method worked the best til now.

 

Upvote 0

P

Patchcord

Board Regular

Joined

Sep 30, 2003

Messages

73

• Sep 13, 2008

• #7

Calculating hours worked within time period

This formula calculates number of hours worked between 2200-0600.

=IF(OR(C10="",D10=""),0,SUMPRODUCT(((MOD(ROW(INDIRECT(C10*1440+1&":"&(D10+(D10<C10))*1440))/1440,1)<--"06:01")+(MOD(ROW(INDIRECT(C10*1440+1&":"&(D10+(D10<C10))*1440))/1440,1)>--"22:00"))/1440)*24)

I'm trying to modify it to calculate hours worked between 1800-2200, but not having much luck.

Any suggestions?

 

Upvote 0

P

Patchcord

Board Regular

Joined

Sep 30, 2003

Messages

73

• Sep 13, 2008

• #8

Apologies, I'm having trouble pasting the complete formula, (should have previewed first) but it relates to the excellent solution posted by Fairwinds.

I'm trying to mod the period from being 2200-0600 to 1800-2200.

I appreciate any assistance. Thank you.

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Sep 18, 2008

• #9

Hi,

Try:

=MAX(0,MIN("22:00",B2+(B2 < A2))-MAX(A2,"18:00"))

 

Last edited: Sep 18, 2008

Upvote 0

P

Patchcord

Board Regular

Joined

Sep 30, 2003

Messages

73

• Sep 18, 2008

• #10

Thanks for your suggestion fairwinds. I'm currently using a solution posted by barry houdini which works well for the current application.

=((A2 > B2)*MEDIAN(0,B2-"18:00","4:00")+MAX(0,MIN("22:00",B2+(A2 > B2))-MAX("18:00",A2)))*24

I'll certainly give yours a try too. It's a good learning experience for me in seeing the different approaches.

Thanks again for your help!

 

Upvote 0