Help need In dates

[vinod22kv]

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

[YoussefBelloum]

Hi,

what is the working hours for you ? it starts and end at what time exactly ?

[vinod22kv]

9 AM TO 6 PM

[YoussefBelloum]

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

[vinod22kv]

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

[sasikanth]

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

[vinod22kv]

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.

[manoj217]

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

[sasikanth · Accepted solution]

Its working fine please find the application attached, attached app for reference