Forum Discussion

Denpowerbi's avatar
Denpowerbi
Regular Visitor
6 years ago
Solved

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. Ec...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

Denpowerbi's avatar
Denpowerbi
Regular Visitor
6 years ago

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]
)