How to assign date/time to certain date/time-range

[MetalKD-Ng]

Hello Gentleman,

Somehow, I am rather sure that this is very likely an allegedly easy question, but I failed.
And now after 3-4 hours of "braining" around trying, I ask for support from your expertise here.

Following problem:

I have got a table with a start- and an end-date/time. (Separated by a tab)

Basically, these are information I got from a log file. (Start- / End-date and time of a production machine for one single component)

StartTime EndTime
16/05/2018 10:02:02 16/05/2018 10:03:57
16/05/2018 10:05:05 16/05/2018 10:05:26
16/05/2018 10:12:04 16/05/2018 10:13:53
16/05/2018 10:16:14 16/05/2018 10:16:34
16/05/2018 11:15:05 16/05/2018 11:16:51
16/05/2018 11:25:42 16/05/2018 11:27:31
16/05/2018 11:28:03 16/05/2018 11:28:24
16/05/2018 11:28:45 16/05/2018 11:29:06
16/05/2018 11:32:21 16/05/2018 11:34:12
16/05/2018 11:38:32 16/05/2018 11:38:52
16/05/2018 11:39:20 16/05/2018 11:39:41
16/05/2018 11:39:57 16/05/2018 11:40:18
16/05/2018 11:40:39 16/05/2018 11:40:59
16/05/2018 11:43:00 16/05/2018 11:43:28
16/05/2018 11:44:50 16/05/2018 11:45:11
16/05/2018 12:21:40 16/05/2018 12:23:41
16/05/2018 12:24:10 16/05/2018 12:24:35
16/05/2018 12:27:38 16/05/2018 12:28:03
16/05/2018 12:29:26 16/05/2018 12:29:51
16/05/2018 12:30:56 16/05/2018 12:31:21
16/05/2018 12:32:22 16/05/2018 12:32:47
16/05/2018 12:33:30 16/05/2018 12:33:55
16/05/2018 12:34:28 16/05/2018 12:34:53
16/05/2018 12:35:25 16/05/2018 12:35:51
16/05/2018 12:35:55 16/05/2018 12:36:21
16/05/2018 13:37:47 16/05/2018 13:39:34


I have got one occurrence, which is 16/05/2018 11:38:40, and now I would like to insert a 3rd column in the table which says "Yes", if the occurrence before is within one line Start- and End-date/time; Otherwise "No".

How to automatically implement this?

PS: Please note: I can have several occurrences the same date (16/05/2018), just at different times.

Is that possible?


Many thanks folks.

I am ***** with this too be honest, even though it is rather likely a very simple task.

regards,

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[63falcondude]

Assuming that all of your dates/times are being seen as numbers (not text), and that your "one occurrence" is in cell H1, you can use something like this:

C2 =IF(AND(A2<=H$1,B2>=H$1),"Yes","No")

If that doesn't do what you were expecting, see post #2.

[MetalKD-Ng]

Hello dear AliGW and falcondude,

Many thanks for your prompt follow up.

I tried to add an attachment with my initial question submission, but failed
Now I got it; Please see attached "LastDates.xlsx"

I amended it for both of your feedbacks.



Worksheet 1 and 4 - "LastDates" show my initial problem.
I would like to assign the 447 occurrences of worksheet 4 to worksheet 1.
Means, basically on page 1 there should be 447 times "yes" and the rest "no", depending on date/time of the occurrence and date/time of the line in worksheet 1.

Explanation:

I am following a log file from a machine.
Worksheet 1 basically shows the uptime. (Depended on n/r of jobs and duration of the same)
Worksheet 4 is a list of Errors where the machine stopped, and I would like to assign the Errors to the Jobs.


Worksheet n/r 2 shows the suggestion from falcondude;
Problem there: It stops after line 447
The formula is for some reason stopped after line 447 - it compares line n/r for n/r I guess?

Worksheet 3 shows what AliGW highlighted.
Result highlighted manually.


Hopefully this helps to shed some light onto my clueless mind... :-)

Many thanks to both of you!

cheers,

[63falcondude]

In post #1 you said that you have one occurrence. In post #4 you said that you have 447 occurrences. Surely you can see the discrepancy here...

Based on the sample that you shared in post #4, you can try this in D2:
=IF(COUNTIFS(_LastDates[LastDates],">="&A3,_LastDates[LastDates],"<="&B3),"yes","no")

Means, basically on page 1 there should be 447 times "yes" and the rest "no", depending on date/time of the occurrence and date/time of the line in worksheet 1.

Only 37 of the dates in the LastDates workbook fit in the time slots in the Tabelle1 worksheet.

[AliGW]

Hello Gentleman,

Just so that you are aware, not all Excel users are male.

[MetalKD-Ng]

Hi Falcondude,

Sure I can see and do agree to the disprepancy...but that is only based on my lack of knowledge and experience. Sorry for that.
Thank you very much for the solution posted in n/r 5, that is perfect; Exactly what I was looking for.
I am really impressed how quickly you have such answer ready at hand. Many thanks. Great.

AliGW: I am sorry for my ignorance! ;-) :-)
Won't happen on future posts again!

Many thanks and enjoy your weekend,

cheers from Austria,
Günter

[63falcondude]

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.