Help with a Formula

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
175
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
BCD
312:3015:00
4START TIMEEND TIMEANSWER
506/08/22 12:3006/08/22 15:00FALSE
606/08/22 06:0006/08/22 22:00
706/08/22 11:3006/08/22 15:00
806/08/22 00:1006/08/22 15:00
906/08/22 14:5906/08/22 17:00
1006/08/22 12:0006/08/22 14:00
1106/08/22 00:0106/08/22 23:00
1206/08/22 05:0006/08/22 13:30
1306/08/22 07:0008/08/22 09:00
Sheet1
Cell Formulas
RangeFormula
D5D5=IF(AND(B3=B5,C3=C5,C5>B5),C5-B5)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,441
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJKLMNOPQ
1
2
31/0/00 12:301/0/00 15:00Hrs MINS
4START TIMEEND TIMEB5-B3B5<$B$3C5=$C$3B5>=$B$3C5>$C$3IF 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
58/6/22 12:308/6/22 15:002:30TRUETRUEFALSEFALSE
68/6/22 6:008/6/22 22:00 TRUEFALSEFALSETRUE
78/6/22 11:308/6/22 15:003:30TRUETRUEFALSEFALSE
88/6/22 0:108/6/22 15:0014:50TRUETRUEFALSEFALSE
98/6/22 14:598/6/22 17:000:01FALSEFALSETRUETRUE
108/6/22 12:008/6/22 14:00 TRUEFALSEFALSEFALSE
118/6/22 0:018/6/22 23:00 TRUEFALSEFALSETRUE
128/6/22 5:008/6/22 13:30 TRUEFALSEFALSEFALSE
138/6/22 7:008/8/22 9:00 TRUEFALSEFALSEFALSE
14
Sheet1
Cell Formulas
RangeFormula
D5:D13D5=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:E13E5=(B5-INT(B5))<($B$3-INT($B$3))
F5:F13F5=(C5-INT(C5))=($C$3-INT($C$3))
G5:G13G5=(B5-INT(B5))>=($B$3-INT($B$3))
H5:H13H5=(C5-INT(C5))>($C$3-INT($C$3))
 

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
175
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
BCDEFG
2FIXed Start Time FIXed End Time
300/01/00 12:3000/01/00 15:00
4Worker START TIMEWorker END TIMEANSWERFormula Required My Formula Correct Answer required
506/08/22 14:0006/08/22 14:001:3012:30-15:002:300:00
606/08/22 14:0006/08/22 22:001:0015:00 - 14:002:301:00
706/08/22 12:3006/08/22 15:002:3015:00 -12:302:302:30
806/08/22 06:0006/08/22 17:0011:0015:00-12:302:302:30
Sheet1
Cell Formulas
RangeFormula
D5D5=C5-B3
G5,G7G5=C5-B5
D8D8=IF(C8>B8,C8-B8,1-B8+C8)
F5:F8F5=IF(AND(B5>$B$3,C5>$C$3),C$3-B$3,IF(AND(B5<$B$3,C5<$C$3),C5-$B$3))
 

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
711
Office Version
  1. 2016
Try this

Book1
ABC
312:3015:00
4START TIMEEND TIMEANSWER
506/08/2022 12:3006/08/2022 15:002:30
606/08/2022 6:0006/08/2022 22:002:30
706/08/2022 11:3006/08/2022 15:002:30
806/08/2022 0:1006/08/2022 15:002:30
906/08/2022 14:5906/08/2022 17:000:01
1006/08/2022 12:0006/08/2022 14:001:30
1106/08/2022 0:0106/08/2022 23:002:30
1206/08/2022 5:0006/08/2022 13:301:00
1306/08/2022 7:0008/08/2022 9:000:00
Sheet1
Cell Formulas
RangeFormula
C5:C13C5=MAX(0,MIN(MOD(B5,1),$B$3)-MAX(MOD(A5,1),$A$3))
 

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
175
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

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

Well-known Member
Joined
Apr 29, 2016
Messages
711
Office Version
  1. 2016
Update

Login tai khoan 3 day.xls
ABC
312:3015:00
4START TIMEEND TIMEANSWER
506/08/2022 13:3007/08/2022 15:004:00
606/08/2022 16:0008/08/2022 13:003:00
Sheet2
Cell Formulas
RangeFormula
C5:C6C5=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))
 
Solution

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
175
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

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

 

Forum statistics

Threads
1,181,218
Messages
5,928,749
Members
436,627
Latest member
caligirl626

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top