Adding time to a date function

I'm not sure I understand. I don't see anything in either =DATE(...) function that would return time information as well. Your description claims that you are getting time information for the 2 Jan date, but not for the 3 Jan date. I cannot replicate that result based on what you wrote.

I would expect a formula that returns both date and time in this fashion would look something like =DATE(2022,1,2)+TIME(6,24,33) to return 2 Jan 2022 at 6:24:33 AM.

A sample file showing what you currently have might help us understand better.

I still don't understand what you are trying to do. You talk about sorting, and your list appears correctly sorted in ascending order, first by date, then by time. You also say something about have the end date end at 17:00. Are you now wanting to filter the list so that you don't see the times after 17:00? On all days or only the last day? Or something else?

I can't see in your sample file where or how you intend to do the filter step. If I assume you are going to use Autofilter for this (because that is the easiest filter that my older version supports):

1) I'm going to want a column in the main list that combines date and time together, so I add a column (I used column B) and add the formula =SUM(C7:D7) and copy/paste/fill to the bottom of the list.
2) set up the autofilter
3) In the date+time dropdown (on column B), I bring up the filter menu, tell it I want to find "between," then fill out the form to find date+time that is after 1/2/2022 04:00 and before 1/3/2022 17:00. List is filtered according to those criteria.

Anytime I want to filter the list by two date/time, I bring up the filter menu in the appropriate column and specify the filter criteria.

If you prefer, your newer version should be able to use the FILTER() function to do the same thing: https://support.microsoft.com/en-us/...c-4877ad80c759

Does that help?

Try:

=LET(A,C7:F1000,B,FILTER(A,INDEX(A,,3)<>0),C,INDEX(B,,1)+INDEX(B,,2),VSTACK(C6:F6,FILTER(B,(C>=N17)*(C<=O17))))

Refer to the file. I may have added some rows/columns...

Are you wanting a FORMULA, or are you wanting to use the advanced filter?

It is beginning to sound like you want to use the advanced filter... as it LOOKS like the OUTPUT data are to be in the same place as the INPUT data.

Please clarify. I'm not very good with Advanced Filter, so if that's what you want, I will step aside.

Show me what you expect to see as the result.

So what is on the sheet is the filtered result.... from the raw data that is not included in the sample?????

when you say filter function... do you mean a FORMULA.... =FILTER(something) or what?

If yes, then we need to see some more of the raw data. If no, please explain. I am totally bewildered by your (non)-explanation.

Where are the raw data? If you want to modify a formula to pull data from place X to place Y... we need a sample of raw data.



The formula I gave you:

=LET(A,C7:F1000,B,FILTER(A,INDEX(A,,3)<>0),C,INDEX(B,,1)+INDEX(B,,2),VSTACK(C6:F6,FILTER(B,(C>=N17)*(C<=O17))))

was designed to work on raw data in columns C to F. If that is NOT the rawe data, we need to know WHERE IT IS.

(in response to your PM) This is why I don't take on private commissions for this sort of thing. Unless you make it 100% clear and 100% POSSIBLE, I'll be walking away from this thread.

No more guessing games.