Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
My requirement is like below..
if event date = same day of event date and also next day before 8am then consider as same day of event date.
if entry done on 06th sep and also entry done on 07th sep till 8:00am then consider that date as 06th Sep only.
please help how to do.
Hi Mayuri,
you only need to subtract 8 hours (= a third of a day) from your timestamp then convert to date
=Date(Num(Timestamp)-1/3)
Regards
Andrew
Hi,
Can you post some example data.
Regards,
Kaushik Solanki
Hi
Do you mean evedate is varied from eg:
12:00 AM 09/07/2016 TO 8:00 AM09/08/2016............would be taken as 09/07/2016 ?
Hi Mayuri,
Try:
=Date(Num(Timestamp)-1/3)
Kind regards
Andrew
Yes exactly Alluraiah Allu
Hi
in the load editor please try this
FYI :Attached the sample excel file worked.....
SOURCE:
LOAD
Time(Date,'HH:MM:SS') AS TIME,
Date
FROM
(ooxml, embedded labels, table is Sheet1);
OUTPUT:
LOAD *,
if(Date<>Previous(Date) and hour(TIME)<=8,Previous(Date),Date) as Event_Date
/***change the FORMAT of Event_Date to MM/DD/YYYY*******
Resident SOURCE;
DROP Table SOURCE;
if Event date is 07th Sep 2016 after 8:00am to 08th Sep 2016 8:00am , then that transaction should show in date as 07th Sep 2016 only
So
we have to change NORMAL DAY range (12 am to 12 AM)..............to DAY(8 AM to 8 AM) ??
EDITED
FYI:
In my example :
9/7/2016 15:55
9/8/2016 3:55
Both will go to 09/07/2016 !!!!
Yah normal day range will be 8:00am today to next day 8:00am
try this
=date(if(frac(num(time(today())))<0.33333333333576,date(today())-1,date(gggg)))