Forum Discussion

joep78's avatar
joep78
Icon for Helper III rankHelper III
5 years ago
Solved

calculate working hours based on multiple text fields

hi all,

 

I want to calculate the working hours per employee but the calculation should be based on text fields: 

 

Here an example how the data look like in the DB (with exeption of the last column, this should be calculated)

 

personTypeOfDayMondayTypeOfDayTuesdayTypeOfDayWednesdayTypeOfDayThursdayTypeOfDayFridayTypeOfDaySaterdayTypeOfDaySundayWorking hours
AWDWDWDWDWDNWDNWD40
BWDWDWDWDNWDNWDNWD32
CWDNWDWDWDNWDWDNWD32
DWDWDNWDNWDNWDNWDNWD16

 

So if a field is filled with WD (working day) the calculation should count this as 8 hours. I of course can create 5 new columns for every typeOfDay and build it like this, but I hope there is a smarter solution without having five column in place. 

 

Hopefully anybody can help me out with this, thanks in advance for a reply!

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    joep78 , You can create a new column like

    if([TypeOfDayMonday]="WD",8,0) + if([TypeOfDayTuesday]="WD",8,0) + if( [TypeOfDayWednesday]="WD",8,0) + if( [TypeOfDayThursday]="WD",8,0) + if( [TypeOfDayFriday]="WD",8,0) + if( [TypeOfDaySaterday]="WD",8,0) + if( [TypeOfDaySunday]="WD",8,0)

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    joep78 , You can create a new column like

    if([TypeOfDayMonday]="WD",8,0) + if([TypeOfDayTuesday]="WD",8,0) + if( [TypeOfDayWednesday]="WD",8,0) + if( [TypeOfDayThursday]="WD",8,0) + if( [TypeOfDayFriday]="WD",8,0) + if( [TypeOfDaySaterday]="WD",8,0) + if( [TypeOfDaySunday]="WD",8,0)

  • joep78's avatar
    joep78
    Icon for Helper III rankHelper III
    5 years ago

    Amit,

     

    Thanks again for a quick response, very helpful again!