problem with date

[manoj217]

Hi i have a requirement,

i have date field which is consisting date like ( 26/6/2018 18:30:44) in this format. if i have this time in after 6.00PM means need to consider as next day morning 9.00AM and if these time have in sunday means need to consider as monday.

in normal working hours there is no issue if it is after working hours means need to change as next working hour.

please help on this.

[olivierrobin]

hello

why don't you create a 2nd date with a formula like :

let date2 = date1 + &if(time(date)>6,1,0)

[tresesco]

Try like:

Load

          Date(If(num(WeekDay(DateWH))=7, DateWH+1,DateWH), 'DD/M/YYYY hh:mm:ss') as FinalDate;

Load

          Date(if(hour(Date)>=18, Floor(Date)+1/24*9, Date), 'DD/M/YYYY hh:mm:ss') as DateWH

From <>;

[manoj217]

Hi Tresesco thanks your reply but I want those two conditions in a single field

[manoj217]

Hi Olivier thanks for your reply it will satisfies only one condition if time is >6 it will add some time it will change next day but I want if it is week end (sat) means upto 1PM need to consider sat only if it is after 1PM means need to consider Monday,

similarly sunday is also holiday so consider as Monday

[jyothish8807]

May be this manoj,

Load Date,

if(subfield(Subfield(Date,' ',2),':',1)>=18,date#(date(Date+1,'DD/MM/YYYY') &' 09:00:00','DD/MM/YYYY hh:mm:ss'),Date)  as Date2;

LOad DAte#(Date,'DD/MM/YYYY hh:mm:ss') as Date;

LOAD * INLINE [

    Date

    26/6/2018 18:30:44

];

[tresesco]

If you run the above script you would get single field as FinalDate. It's only that the two conditions are handled in two steps using preceding load. Try and let know.

[jyothish8807]

Try this:

Load Date,

if(subfield(Subfield(Date,' ',2),':',1)>=18 and not match(weekday(Date),'Sat','Sun'),date#(date(Date+1,'DD/MM/YYYY') &' 09:00:00','DD/MM/YYYY hh:mm:ss'),

if( match(weekday(Date),'Sat') and subfield(Subfield(Date,' ',2),':',1)>=13,date#(date(Date+2,'DD/MM/YYYY') &' 09:00:00','DD/MM/YYYY hh:mm:ss'),

if( match(weekday(Date),'Sun'),date#(date(Date+1,'DD/MM/YYYY') &' 09:00:00','DD/MM/YYYY hh:mm:ss'),

Date)))  as Date2;

LOad DAte#(Date,'DD/MM/YYYY hh:mm:ss') as Date;

LOAD * INLINE [

    Date

    26/6/2018 18:30:44

    30/6/2018 18:30:44

    24/6/2018 18:30:44

];

[vinod22kv]

Hi Jyothish,

Can you please reply to below link

Help need In dates

Thanks and Regards,

Vinod.