#### mbpaul1987

##### New Member

- Joined
- Jan 15, 2014

- Messages
- 15

I need to develop a Bill Rate calculator for employees that are being hired on. I currently have an Employee database with start dates, employee categorization types (5 columns), Rate Date, Hourly rate, Bill rate, + additional columns. The bill rate needs to be calculated based on the categorization types (there are 15 formula variations). That in itself is pretty straight forward however where it is getting complicated is that the rate calculations change every 6 months. So I need to be able to reference 1 of 15 formulas based on the categorization types filled in, then use the rate calculation data for the rate date. As a simple example:

Database:

Name | Category 1 | Category 2 | Category 3 | Category 4 | Rate Date | Hourly Rate | Bill Rate |
---|---|---|---|---|---|---|---|

Joe | Yes | Yes | Yes | Yes | Jan-2019 | 20 | 25.75 (Formula 1 & Jan-2019 bill rates) |

Frank | Yes | No | No | Yes | Jun-2019 | 20 | 22.70 (Formula 2 & Jun-2019 bill rates) |

Harry | Yes | Yes | Yes | Yes | Jun-2019 | 20 | 26.95 (Formula 1 & Jun-2019 bill rates) |

Bob | Yes | No | No | No | Jan-2020 | 20 | 24.20 (Formula 3 & Jan-2020 bill rates) |

Bill Rates

Rate Date | Pension Ratio | Savings | Employment Insurance Ratio (E.I.) | Education Fund | Union Dues |
---|---|---|---|---|---|

Jan-2019 | .04 | $2.00 | .06 | $1.50 | $.25 |

Jun-2019 | .05 | $2.50 | .07 | $1.75 | $.30 |

Jan-2020 | .06 | $3.00 | .08 | $2.00 | $.35 |

Formula Number | Category 1 | Category 2 | Category 3 | Category 4 | Formula |
---|---|---|---|---|---|

1 | Yes | Yes | Yes | Yes | =Hourly Rate + (Hourlyrate*Pension Ratio)+Savings+(Hourlyrate*E.I. Ratio)+Education Fund+Union dues |

2 | Yes | No | No | Yes | =Hourly Rate + (Hourlyrate*Pension Ratio)+(Hourlyrate*E.I. Ratio)+Union dues |

3 | Yes | No | No | No | =Hourly Rate + (Hourlyrate*Pension Ratio)+Savings |

I need to be able to calculate that bill rate in the database. Any assistance with ideas would be greatly appreciated.

Thank You