time interval within 12hrs

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
444
Office Version
  1. 2019
Hi All, how can i calculate below time interval which is less than 12hrs from 2 cells, is it possible to use in conditional formatting?
11:30 - 22:0008:00 - 17:30wrong
10:00 - 19:3011:30 - 21:00correct
thanks so much
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
sorry for my poor english presenting. i try to make it simple with below table.
1) if i compare within 1 week, it should be fine as every staff assume to compare with same row, different column
2) if i compare week 1 saturday to week 2 sunday, it may comes with error caused by same staff, but different row, like a new staff come.
week 1saturdayweek 2sunday
BEE09:15 - 18:45BEE09:15 - 18:45=IFERROR(IF(OR(RIGHT(B2,5)+0=0,LEFT(E2,5)+0=0),"",IF(RIGHT(B2,5)+0>LEFT(E2,5)+0.5,"less than 12hr","")),"")
BRENDA12:00 - 21:30BILL (NEW STAFF)this is new staff, i bet the outcome is blank, just like day off vs anytime, leave it blank
BRIANBRENDA08:00 - 18:00since week 2 come with a new staff, brenda will go down one row, thus no outcome no matter less than / good enough
BRIAN13:00 - 21:00same with brenda case, just go down one row caused by a new staff came
i think i need a vlookup or match name formula first?
thank you so so much, fluff
 
Upvote 0
How about
=IFERROR(IF(OR(RIGHT(INDEX($B$2:$B$6,MATCH(D2,$A$2:$A$6,0)),5)+0=0,LEFT(E2,5)+0=0),"",IF(RIGHT(INDEX($B$2:$B$6,MATCH(D2,$A$2:$A$6,0)),5)+0>LEFT(E2,5)+0.5,"less than 12hr","")),"")
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Hi Fluff,
can this formula be used in this case when my employee applied leave like this table?
thank you very much

Book1
ABDFHJLNPQ
117.526.2526.25#VALUE!#VALUE!#VALUE!17.5
2
3Bee09:30 - 19:0011:30 - 21:0011:30 - 21:0012:00 - 21:3012:00 - 21:30#VALUE!
4Brenda00:00 - 00:0009:15 - 18:4509:15 - 18:45Unpaid Leave Day 1.0Unpaid Leave Day 1.0Unpaid Leave Day 1.0#VALUE!
5Brian09:15 - 18:4509:15 - 18:4510:00 - 19:3011:30 - 21:0010:00 - 19:30#VALUE!
6Carlie09:15 - 18:4509:15 - 18:4512:00 - 21:30Vacation Leave Day 1.0Vacation Leave Day 1.0#VALUE!
Sheet1
Cell Formulas
RangeFormula
B1, N1, L1, J1, H1, F1, D1B1=(SUMPRODUCT(RIGHT("00:00"&B3:B6,5)-LEFT(B3:B6&"00:00",5))-("00:45"*COUNTIFS(B3:B6,"?*",B3:B6,"<>00:00 - 00:00")))*24
Q3:Q6Q3=(SUMPRODUCT(RIGHT("00:00"&B3:O3,5)-LEFT(B3:O3&"00:00",5))-("00:45"*COUNTIFS(B3:O3,"?*",B3:O3,"<>00:00 - 00:00")))*24
 
Upvote 0
Where are you trying to put the formula?
 
Upvote 0
column B, D, F.... in row 1, is used to count total hr of all employee for the day
column Q in every row, is used to count the specific employee week total
so, i basically need a formula in B1, D1, F1, H1, J1, L1, N1 & Q3....

thanks so much
 
Upvote 0
But you already have formulae in those cells & they have nothing to do with this thread.
Should you have posted this to another of threads?
 
Upvote 0
But you already have formulae in those cells & they have nothing to do with this thread.
Should you have posted this to another of threads?
H1, J1, L1 & column Q seems doesnt working fine, and might coursed by the inappropriate time value, like "Unpaid Leave Day 1.0" / "Vacation Leave Day 1.0"?
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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
Back
Top