Forum Discussion

drinko's avatar
drinko
Helper I
5 years ago
Solved

restart calculated counter on max value

I have a column called Pay Period that I have calculated based on a date, but its not a regular calendar schedule so I can't use any date functions for this.  Basically I am counting pay periods for ...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello drinko 

     

    a simple solution would be to use nested ifs like this

    PayPeriod = if(
    (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)<27, 
    (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1), 
    if(
        (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)>26&&(FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)<53, 
        (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)-26,
        blank()//etc.....
    )
)


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello drinko 

 

a simple solution would be to use nested ifs like this

PayPeriod = if(
    (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)<27, 
    (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1), 
    if(
        (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)>26&&(FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)<53, 
        (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)-26,
        blank()//etc.....
    )
)


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy