Excelexcel86
Board Regular
- Joined
- Feb 28, 2023
- Messages
- 99
- Office Version
- 365
- Platform
- Windows
- Mobile
Hi guys someone very kindly managed to help me with the following formula =IF(MEDIAN(MOD(A1,1),TIME(6,0,0),TIME(14,30,0))=MOD(A1,1),"Y","N")
Now this works to an extent but my problem is instead of yes no I’ve I’ve changed the yes to B2-A2 this is because my cells contains dates and times in that are always different so if the two times in cell A and b are in between 06.00 and 14.30 it works but if cell b contains a different date the time formula don’t work as it goes to whatever time is in that cell and outputs the value below is an example of what is happening .so is there an And command I can use with this formula so it also looks to see if both dates are the same as well this would then work as both would be true
Now this works to an extent but my problem is instead of yes no I’ve I’ve changed the yes to B2-A2 this is because my cells contains dates and times in that are always different so if the two times in cell A and b are in between 06.00 and 14.30 it works but if cell b contains a different date the time formula don’t work as it goes to whatever time is in that cell and outputs the value below is an example of what is happening .so is there an And command I can use with this formula so it also looks to see if both dates are the same as well this would then work as both would be true
01/03/2023 06:30 | 01/03/2023 13.30 | 6.00 | works fine |
01/03/2023 | 01/03/2023 15:30 | Over work hours | Works fine |
02/03/2023 08:00 | 03/03/2023 07:00 | 23.00 | Don’t work because it’s gone over to 06:00 in the morning it’s calculate over the day |