Forum Discussion
New column with Calculated Date
Ciao a tutti,
devo calcolare una nuova data ordine sapendo che
_se la data ordine è stata inserita> 16.00 devo aggiungere 1 giorno.
_se la data calcolata ricade in un giorno festivo (sab. Dom. Ecc) devo posticiparla al primo giorno lavorativo.
Ho inserito una tabella calendario con i soli giorni lavorativi, con la funzione "SE" riesco a posticipare in un giorno i dati calcolati MA non riesco in nessun modo a posticipare la data al primo giorno utile lavorativo
Sono bloccato su questo punto, spero che qualcuno mi possa aiutare
grazie in anticipo!
HI Denpowerbi,
I think you need to add conditions to if statement to check its weekday and hour. Please take a look at following calculate column formula if it meets your requirement:
NewDate = IF ( HOUR ( [Datetime] ) >= 16, IF ( WEEKDAY ( [Datetime], 2 ) >= 5, DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 7 - WEEKDAY ( [Datetime], 2 ) + 1 ), DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 1 ) ), IF ( WEEKDAY ( [Datetime], 2 ) >= 5, DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 7 - WEEKDAY ( [Datetime], 2 ) + 1 ), [Datetime] ) )Regards,
Xiaoxin Sheng
4 Replies
for checking the time you can use
Try in new column something like this
if([date].time > time(16,0,0) ,[date]+1,[date])
do add subtract working day see if my blog can help
Grazie, per controllare il tempo ok.
Ho guardato il tuo blog, molto interessante ma non riesco ancora a gestire i dati +1 solo nei giorni di lavoro.
HI Denpowerbi,
I think you need to add conditions to if statement to check its weekday and hour. Please take a look at following calculate column formula if it meets your requirement:
NewDate = IF ( HOUR ( [Datetime] ) >= 16, IF ( WEEKDAY ( [Datetime], 2 ) >= 5, DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 7 - WEEKDAY ( [Datetime], 2 ) + 1 ), DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 1 ) ), IF ( WEEKDAY ( [Datetime], 2 ) >= 5, DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 7 - WEEKDAY ( [Datetime], 2 ) + 1 ), [Datetime] ) )Regards,
Xiaoxin Sheng
Thank you very much, I solved my problem with a small change to your suggestion.
NewDate
IF (
'OSC Sales Header :' [Now]> TIMEVALUE ("15:59:59");
IF (
WEEKDAY ([Date Order]; 2)> : 5;
DATE (YEAR([ORDER DATE]); MONTH ([Order Date]); DAY ([ORDER DATE]) : 7
- WEEKDAY ([Order Date]; 2) : 1);
DATE (YEAR ([Order Date]); MONTH ([Order Date]); DAY ([Order Date]) : 1)
);
"OSC Sales Header " [Order Date]
)
