Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to sort list of start&end dates with the ID if it falls within the next 24 hours. For example. Equipment ID, Start date, End date 7017, 9/08/2018 6:00, 10/08/2018 6:00 7017,9/08/2018 6:00,9/08/2018 12:00 5065, 13/08/2018 6:00, 18/08/2018 6:00 want to automate the list so that it populates the ID start&end date if it falls with in the next 24 hours of today. problem is some times the end date is 3 days out, and i want to list the ID, and end date (equipment handed back) if the end date falls within next 24 hours.
Solved by T. H. in 56 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 09/08/2018 - 07:52
HI
User 09/08/2018 - 07:52
hi
Excelchat Expert 09/08/2018 - 07:52
Could you please share the file
Excelchat Expert 09/08/2018 - 07:53
if possible
User 09/08/2018 - 07:53
stuck on a problem. ive attached a spreadsheet
User 09/08/2018 - 07:53
attached
[Uploaded an Excel file]
Excelchat Expert 09/08/2018 - 07:53
okay
Excelchat Expert 09/08/2018 - 07:53
give me a moment to open the file
User 09/08/2018 - 07:53
no problem :)
Excelchat Expert 09/08/2018 - 07:54
I have the file open
Excelchat Expert 09/08/2018 - 07:54
Could you please elaborate on the problem?
User 09/08/2018 - 07:54
no worres.
User 09/08/2018 - 07:55
i want a auto output, of what equipment Id is needed to be handed in. hence, if start date of 7017 is within next 24 hr then list them on the output results list.
User 09/08/2018 - 07:56
also, if the end date is approaching and is within the next 24 hours it also needs to be listed on the "output results" list.
User 09/08/2018 - 07:57
i cant do a vlookup because there are more than 1 identical Ids on the list with 2 date ranges.
Excelchat Expert 09/08/2018 - 07:57
For each row, we can check, right?
Excelchat Expert 09/08/2018 - 07:57
of table A,B,C
Excelchat Expert 09/08/2018 - 07:58
Or, in the same table just add a flag if it is due?
Excelchat Expert 09/08/2018 - 07:58
If either of the conditions meet, then 1 else 0
Excelchat Expert 09/08/2018 - 07:58
Would this solve your problem
User 09/08/2018 - 07:59
flag it on the "yellow" "outputs results" E,F, G column. if its due
Excelchat Expert 09/08/2018 - 07:59
okay
Excelchat Expert 09/08/2018 - 07:59
give me sometime to work
User 09/08/2018 - 07:59
Also need it to flag if its needed to be handed in within 24 hours.. i.e. the start date.
User 09/08/2018 - 08:01
issue i have is, there are more than 2 IDs on the list with 2 separate dates ranges.. i've gotten around it by artifically recreating the ID by adding 7017"_" to make it unique for th vlookup.
User 09/08/2018 - 08:01
Can you tell me if you can find an alternate solution, that doesnt make it so complicated.
Excelchat Expert 09/08/2018 - 08:03
I am thinking of using IF condition and generating a flag for each row and if that flag = 1, then i will list it out in the column E,F,G
Excelchat Expert 09/08/2018 - 08:03
Does this sound right?
User 09/08/2018 - 08:03
give it a try and send me a copy
Excelchat Expert 09/08/2018 - 08:03
sure
User 09/08/2018 - 08:03
the output just needs to be, "If start date falls inside next 24 hours, then list all ID, with start & end date..
User 09/08/2018 - 08:04
If end date falls within 24 hours, also list them in the list.
User 09/08/2018 - 08:04
however we dont want double ups.
Excelchat Expert 09/08/2018 - 08:04
okay
Excelchat Expert 09/08/2018 - 08:04
give me sometime to work on it
User 09/08/2018 - 08:04
ok thanks
User 09/08/2018 - 08:07
please send a copy to email: Eddyng88@gmail.com
User 09/08/2018 - 08:08
if you figure it out.
Excelchat Expert 09/08/2018 - 08:19
Here you go
[Uploaded an Excel file]
User 09/08/2018 - 08:20
hi,
Excelchat Expert 09/08/2018 - 08:21
hey
User 09/08/2018 - 08:21
but the 2nd colum doenst work.
User 09/08/2018 - 08:21
7017, 9/08/2018 6:00
User 09/08/2018 - 08:21
is inside now.
User 09/08/2018 - 08:22
the date is within the the date ranges in A2, B2
Excelchat Expert 09/08/2018 - 08:22
I have just consider in the next 24 hour
Excelchat Expert 09/08/2018 - 08:22
DO you want me to consider in theprevious 24 hour also
Excelchat Expert 09/08/2018 - 08:22
?
User 09/08/2018 - 08:22
no
User 09/08/2018 - 08:23
sorry, prob didnt explain properly.
Excelchat Expert 09/08/2018 - 08:23
What's the time zone for dates in the excel?
User 09/08/2018 - 08:23
the dates are in Australia format
User 09/08/2018 - 08:24
english Austraia
Excelchat Expert 09/08/2018 - 08:24
okay
Excelchat Expert 09/08/2018 - 08:24
In terms of GMT, what is it?
Excelchat Expert 09/08/2018 - 08:24
I can modify the formula accordingly
User 09/08/2018 - 08:24
+8
Excelchat Expert 09/08/2018 - 08:24
okay
User 09/08/2018 - 08:24
nah was hoping to reference the 2 B1, B2 cells.
Excelchat Expert 09/08/2018 - 08:24
I will modify it
User 09/08/2018 - 08:25
so that if its between the 2 date cells, then it would list
User 09/08/2018 - 08:25
just incase i want to increase the date range. from 24 hrs to 36.. if i wanted to
User 09/08/2018 - 08:26
the date range must start from today 6am to next day 6am.
Excelchat Expert 09/08/2018 - 08:26
I am not able to follow
User 09/08/2018 - 08:26
however in this example i've put 36hrs
Excelchat Expert 09/08/2018 - 08:27
As per my formula, the second one would not list because, both the dates are past as per the current time
Excelchat Expert 09/08/2018 - 08:28
I hope this makes sense
User 09/08/2018 - 08:28
yes it does.
Excelchat Expert 09/08/2018 - 08:29
Do you want me to list the second one also?
User 09/08/2018 - 08:29
instead of using "Now"
User 09/08/2018 - 08:30
could you have it reference the date on the top left hand corner?
Excelchat Expert 09/08/2018 - 08:30
yes
Excelchat Expert 09/08/2018 - 08:31
Date in B1?
User 09/08/2018 - 08:31
yep
Excelchat Expert 09/08/2018 - 08:31
okay
User 09/08/2018 - 08:31
date range in B1 , B2
User 09/08/2018 - 08:31
if date falls inside B1 and B2
Excelchat Expert 09/08/2018 - 08:32
If any of the dates fall between B1 and B2, you want to list them, right?
User 09/08/2018 - 08:32
yes
Excelchat Expert 09/08/2018 - 08:32
okay
Excelchat Expert 09/08/2018 - 08:32
working on it
User 09/08/2018 - 08:32
thx
Excelchat Expert 09/08/2018 - 08:35
Updated
[Uploaded an Excel file]
User 09/08/2018 - 08:48
thx

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.