Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the E Mail received date and time field as given bellow. and my working hrs. is b/w 9 am to 6 pm. now I have to create the new date and time field with this conduction if the mail receives in non-working hrs. then the date and time has to change to next day working hrs. That is next day 9 am.
and in Saturday working hrs. is up to 3 pm and mails received after 3 pm and Sunday has to be added to Monday morning 9 am.
Sample data is attached below
Can you please please help me with the Sample QVW.
Thanks and Regards,
Vinod.
5/26/2018 9:35 |
5/26/2018 9:36 |
5/26/2018 9:40 |
5/26/2018 9:40 |
5/26/2018 9:40 |
5/26/2018 9:40 |
5/26/2018 9:40 |
5/26/2018 9:40 |
5/26/2018 9:41 |
5/26/2018 9:42 |
5/26/2018 9:43 |
5/26/2018 9:43 |
5/26/2018 9:44 |
5/26/2018 9:44 |
5/26/2018 9:44 |
Its working fine please find the application attached, attached app for reference
Hi,
what is the working hours for you ? it starts and end at what time exactly ?
9 AM TO 6 PM
try this:
=if( Date(TimeStamp#(FIELD,'M/DD/YYYY hh:mm'),'WWWW')='Saturday' and Timestamp(TimeStamp#(FIELD,'M/DD/YYYY hh:mm'),'hh') > 3, TimeStamp#(FIELD,'M/DD/YYYY hh:mm')+2,
if(Timestamp(TimeStamp#(FIELD,'M/DD/YYYY hh:mm'),'hh') > 6 or Timestamp(TimeStamp#(FIELD,'M/DD/YYYY hh:mm'),'hh') <9, TimeStamp#(FIELD,'M/DD/YYYY hh:mm')+1) as FIELD
Hi,
Thank for your reply.
But if i receive the mail at 8 pm as per your code it will add one day to it. So it will become next day 8 pm still it will not comes under working hrs....
Thanks and Regards,
Vinod
HI
Try below code in your script
LOAD [Date and Time],
IF( ((WeekDay([Date and Time])='Sun') or (WeekDay([Date and Time])='Sat' and Hour(([Date and Time]))>15)),
timestamp(Timestamp( weekstart([Date and Time]+1)) +(540/24/60)),
IF( (WeekDay([Date and Time])<>'Sun' and WeekDay([Date and Time])<>'Sat' and ( Hour(([Date and Time]))>15)),
timestamp(DayStart([Date and Time],1)+(540/24/60)),[Date and Time] )) as NewDate
FROM
(ooxml, embedded labels, table is Sheet1);
Thanks
Hi,
I have checked your script,
I have selected 05/20/2018 7:00:05 pm and new date is showing 05/20/2018 9:00:00:am this is wrong date has to be 21 inested of 20.
Thanks and Regards,
Vinod.
Load Date,
if(subfield(Subfield(Date,' ',2),':',1)>=18 and not match(weekday(Date),'Sat','Sun'),date#(date(Date+1,'M/DD/YYYY') &' 09:00:00','M/DD/YYYY HH:MM'),
if( match(weekday(Date),'Sat') and subfield(Subfield(Date,' ',2),':',1)>=15,date#(date(Date+2,'M/DD/YYYY') &' 09:00:00','M/DD/YYYY HH:MM'),
if( match(weekday(Date),'Sun'),date#(date(Date+1,'M/DD/YYYY') &' 09:00:00','M/DD/YYYY HH:MM'),
Date))) as Date2
Its working fine please find the application attached, attached app for reference