Log In
Register
Home
New Member
March 22, 2020
Offline
Hello,
I watching youtube and Excel documentation, but there are just simple examples which I cant relate to my project.
I dont understand what kind syntax must be used in order to sort dates with hours.
Example:
Every day I need to sort dates with hours. Format 3/24/2020 0:00 in the cell. and i need to make condition which shows:
Mark dates which are older now()-1 and of that last oldest day hour 8:00
.
today is 3/21/2020 11:58
show and mark me:
3/20/2020 9:01 - do not mark
3/19/2020 7:58 - this date SHOULD be selected and marked, because it is less than 8:00 AM
3/19/2020 8:01 - this date should NOT be selected and marked, because it is good time from 8:00 AM
3/18/2020 9:01 - mark this one because it is older
July 16, 2010
Offline
Hi John,
Can you please expand on some of the logic? e.g. why should the first date/time 3/20/2020 9:01 not be marked? Likewise, why are we marking the last date but not the first date? They're both older and both have time of 9:01.
That said, to point you in the right direction, you need to separately test the date and time. To separate the date from the time you can use the following formulas where your date/time is in cell A1:
Extract Date: =INT(A1)
Extract Time: =A1-INT(A1)
Date/Times are stored in Excel as date/time serial numbers as explained here.
I hope that helps.
Mynda
New Member
March 22, 2020
Offline
Hi Mynda,
Thank you for your commet. No problem I will explain the logic one more time.
I have sort and show which cases are older than (today day - 1 day and that last day time) for example:
today we have
3/22/2020 9:01 - Today is Good
3/21/2020 11:01- Yeasterday day good
3/20/2020 8:01 - 2 days old case is good but only from 8:00 hour, old but do not mark
3/20/2020 7:01 - old and mark because it is older 2 days and time is below 8:00 hour
3/19/2020 9:01 - old and mark
3/17/2020 9:01- old and mark
Some guy gave me a hint, and it seems the code is correct, but I am nor sure why this code not working for me if I place it as usual to formula field.
To sort, you can use a helper column with:
=AND(C2<$E$1-1,MOD(C2,1)<8/24)*1
for cond. format: =AND($C2<$E$1-1,MOD($C2,1)<8/24)
I solved it with marcros by recording all moves (had troble with macros speed, it is to fast and code was crashing), - by creating separate table, extracting hour(), sorting and deleting all necessary information. Thank you for reply and some guidance looking for right answer. John
1 Guest(s)
