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.