Help with a Formula

[Lukma]

Hi Friends

I need a help with a formula that will only check the time in a date & time and subtraction base on Column B3 and C 3

what is need is need a worker is meant to start at 12:30 and end 15:00 in each day so if the work should start work by 6am or 7am the formula should take to subtract the end time from 12:30
IF Column B5 Time is less than Column B3 time and column C5 time is equal to Column C3 then subtract C5-B5
IF Column B5 Time is Equal or Grater than B3 time and Column C5 is Grater Than C3 The Subtract C3-B5

I hope any one will understand all i need in formula

Book2 (version 1).xlsb
	B	C	D
3	12:30	15:00
4	START TIME	END TIME	ANSWER
5	06/08/22 12:30	06/08/22 15:00	FALSE
6	06/08/22 06:00	06/08/22 22:00
7	06/08/22 11:30	06/08/22 15:00
8	06/08/22 00:10	06/08/22 15:00
9	06/08/22 14:59	06/08/22 17:00
10	06/08/22 12:00	06/08/22 14:00
11	06/08/22 00:01	06/08/22 23:00
12	06/08/22 05:00	06/08/22 13:30
13	06/08/22 07:00	08/08/22 09:00
Sheet1

Cell Formulas
Range	Formula
D5	D5	=IF(AND(B3=B5,C3=C5,C5>B5),C5-B5)

 

[etaf]

because you have dates in column B - but no date in B3 - it will take the date as 1/1/1900
=(B5-INT(B5))<($B$3-INT($B$3))

you say

and column C5 time is equal to Column C3 then subtract C5-B5

do you mean only equal = ????

does this make sense for your example

i have in columns E , F, G, H
shown the formula for each part of your statement

IF Column B5 Time is less than Column B3 time and column C5 time is equal to Column C3 then subtract C5-B5

IF Column B5 Time is less than Column B3 time

Column E

and column C5 time is equal to Column C3 then

Column F
now as you Say AND - both columns need to show a TRUE

Same for the columns G & H

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		1/0/00 12:30	1/0/00 15:00	Hrs MINS
4		START TIME	END TIME	B5-B3	B5<$B$3	C5=$C$3	B5>=$B$3	C5>$C$3				IF Column B5 Time is less than Column B3 time and column C5 time is equal to Column C3 then subtract C5-B5 IF Column B5 Time is Equal or Grater than B3 time and Column C5 is Grater Than C3 The Subtract C3-B5
5		8/6/22 12:30	8/6/22 15:00	2:30	TRUE	TRUE	FALSE	FALSE
6		8/6/22 6:00	8/6/22 22:00		TRUE	FALSE	FALSE	TRUE
7		8/6/22 11:30	8/6/22 15:00	3:30	TRUE	TRUE	FALSE	FALSE
8		8/6/22 0:10	8/6/22 15:00	14:50	TRUE	TRUE	FALSE	FALSE
9		8/6/22 14:59	8/6/22 17:00	0:01	FALSE	FALSE	TRUE	TRUE
10		8/6/22 12:00	8/6/22 14:00		TRUE	FALSE	FALSE	FALSE
11		8/6/22 0:01	8/6/22 23:00		TRUE	FALSE	FALSE	TRUE
12		8/6/22 5:00	8/6/22 13:30		TRUE	FALSE	FALSE	FALSE
13		8/6/22 7:00	8/8/22 9:00		TRUE	FALSE	FALSE	FALSE
14
Sheet1

Cell Formulas
Range	Formula
D5:D13	D5	=IF(AND((B5-INT(B5))<($B$3-INT($B$3)),(C5-INT(C5))=($C$3-INT($C$3))),C5-B5,IF(AND((B5-INT(B5))>=($B$3-INT($B$3)),(C5-INT(C5))>($C$3-INT($C$3))),($C$3-INT($C$3))-(B5-INT(B5)),""))
E5:E13	E5	=(B5-INT(B5))<($B$3-INT($B$3))
F5:F13	F5	=(C5-INT(C5))=($C$3-INT($C$3))
G5:G13	G5	=(B5-INT(B5))>=($B$3-INT($B$3))
H5:H13	H5	=(C5-INT(C5))>($C$3-INT($C$3))

 

[Lukma]

Etaf

Thank you, and i total agreed with you but if i will explain better with what am trying to get in formula

The Start Time is set and fix for the workers which 12:30 so even though the worker start job at 6this wont be consider but to consider 12:30 as start time, even though the worker start early also if the worker end time is grater than 15:00 the the formula should also consider the 15:00 as the end time

i have place a sample in below and i also try some formula but i don't think am able to get it right In Column F5 but the correct answer i required is Column G

because the workers are not allowed to work more than 2:30 only but are the date & time Changes in Column B6 & C6 so this is challenging for me that is why i need a help with the formula IF with multiple condition

Thanks

Book2 (version 1).xlsb
	B	C	D	E	F	G
2	FIXed Start Time	FIXed End Time
3	00/01/00 12:30	00/01/00 15:00
4	Worker START TIME	Worker END TIME	ANSWER	Formula Required	My Formula	Correct Answer required
5	06/08/22 14:00	06/08/22 14:00	1:30	12:30-15:00	2:30	0:00
6	06/08/22 14:00	06/08/22 22:00	1:00	15:00 - 14:00	2:30	1:00
7	06/08/22 12:30	06/08/22 15:00	2:30	15:00 -12:30	2:30	2:30
8	06/08/22 06:00	06/08/22 17:00	11:00	15:00-12:30	2:30	2:30
Sheet1

Cell Formulas
Range	Formula
D5	D5	=C5-B3
G5,G7	G5	=C5-B5
D8	D8	=IF(C8>B8,C8-B8,1-B8+C8)
F5:F8	F5	=IF(AND(B5>$B$3,C5>$C$3),C$3-B$3,IF(AND(B5<$B$3,C5<$C$3),C5-$B$3))

 

[Phuoc]

Try this

Book1
	A	B	C
3	12:30	15:00
4	START TIME	END TIME	ANSWER
5	06/08/2022 12:30	06/08/2022 15:00	2:30
6	06/08/2022 6:00	06/08/2022 22:00	2:30
7	06/08/2022 11:30	06/08/2022 15:00	2:30
8	06/08/2022 0:10	06/08/2022 15:00	2:30
9	06/08/2022 14:59	06/08/2022 17:00	0:01
10	06/08/2022 12:00	06/08/2022 14:00	1:30
11	06/08/2022 0:01	06/08/2022 23:00	2:30
12	06/08/2022 5:00	06/08/2022 13:30	1:00
13	06/08/2022 7:00	08/08/2022 9:00	0:00
Sheet1

Cell Formulas
Range	Formula
C5:C13	C5	=MAX(0,MIN(MOD(B5,1),$B$3)-MAX(MOD(A5,1),$A$3))

 

[Lukma]

Phuoc​

Thanks a lot with the formula it works, but i also have one concern is it possible to have the formula update since the start time in Column A5 and the End Date & Time will continue changing so i need the next day to be added 2.30 to the existing time in Column Column C for each worker because the end date and time will continue so let say

Start Date & Time 6/08/22 05:00 and End Date & Time 06/08/22 13:30 = 1:00 then the end date keep changing to 07/08/22 15:00 then to add 2:30 plus 1.00 = Answer will be 3:30

is this possible

Appreciate if its possible for this formula

Regards

 

[Phuoc]

Update

Login tai khoan 3 day.xls
	A	B	C
3	12:30	15:00
4	START TIME	END TIME	ANSWER
5	06/08/2022 13:30	07/08/2022 15:00	4:00
6	06/08/2022 16:00	08/08/2022 13:00	3:00
Sheet2

Cell Formulas
Range	Formula
C5:C6	C5	=IF(INT(A5)=INT(B5),MAX(0,MIN(MOD(B5,1),$B$3)-MAX(MOD(A5,1),$A$3)), MAX(0,$B$3-MAX(MOD(A5,1),$A$3))+MAX(0,MIN(MOD(B5,1),$B$3)-$A$3)+(INT(B5)-INT(A5)-1)*($B$3-$A$3))

 

[Lukma]

Phuoc​

Thanks for a lot for your solution it really help me a lot am much grateful,

Please could you look into below post i need a solution with a formula for my below post
also please advise me if i can report the blow without going against the rules of the forum as it was post by me previously till waiting for a solution with the formula

Help With Correction of Formula in my Data.

Hi Friends Please i need a help with my formula in Cell D 4, 5, 6, 7, 8 my objective is to count base on Multiple criteria so 1. Count Cell Q base on Multiple Range O & N & K and Criteria But my Problem is that in the Range N is base on IF less that or equal to 69% and the other is If...

www.mrexcel.com