0

I hope I can explain well as I cannot post pictures. I have a google sheet where i have 4 columns with dates

Job Start Date - Col53 Break Date - Col54 Return Date - Col55 Job End Date - Col56

The first 52 columns are personal details of employees which are not important in this case.

I need to use query and derive a sheet "On Site" which shows the employees on the job at the moment.

The function I used is

=QUERY({B11:BG}, "select Col1, Col2, Col4, Col5, Col6, Col10, Col52, Col53 where Col2 is not null and Col53 is not null and Col54 is null or Col54 > date '"&Text(Today(),"yyyy-mm-dd")&"' and Col56 is null",1)

This works well. however, this only shows employees, who have:

  1. not gone on the break (Col53 not null + Col56 null)
  2. who will go on a break after today (Col54 is null or Col54 > date '"&Text(Today(),"yyyy-mm-dd")&"')

But In case we put an older date in Col54(Break Date) and Col55(Return Date), it does not show up in the list as the date in Col54 is "<" today.

But if you put in the criteria Col54 < date '"&Text(Today(),"yyyy-mm-dd")&"' it will then depend on Col55. Col55 must be "<" today as well which shows the employee is on site.

Would it be possible to add an if function or something in the middle of the query? I might need a dynamic criteria which includes both Col54 and Col55 simultaneously.

devanshah
  • 1
  • 2
    Welcome. *"I hope I can explain well as I cannot post pictures."* You can post images. they will show up as links. Also, please read how to and [share a test sheet](https://webapps.stackexchange.com/a/138383/117311) so as you can be easier helped. – marikamitsos May 20 '21 at 15:51
  • But it would be much more valuable to get a link to a real Google sheet, not just an image, with data that demonstrates the problem. – Mike Steelson May 20 '21 at 16:20

0 Answers0