Creating a salary change date list

J

JazzzyJo

Board Regular
Joined
Jul 12, 2011
Messages
60
Hi

I am trying to generate a list of date at which the user changes salary. Theres several conditions and it is way out of my league to do.

Those are the input I am requesting from the user.


Class of employment :

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=312 border=0><COLGROUP><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" span=12 width=26><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 20pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=26 height=21></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 20pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=26></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 20pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=26></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 20pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=26></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 20pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=26></TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 100pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #a5a5a5" width=130 colSpan=5>Effective Date</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 20pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=26></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 20pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=26></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #a5a5a5" colSpan=4 height=21>CREW CHIEF</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8">Day</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8" colSpan=4>Month</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8" colSpan=2>Year</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #a5a5a5; mso-ignore: colspan" colSpan=2 height=21>ACA</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #a5a5a5"></TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #a5a5a5"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8">Day</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8" colSpan=4>Month</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8" colSpan=2>Year</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #a5a5a5; mso-ignore: colspan" colSpan=3 height=21>NON-CERT</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #a5a5a5"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8">Day</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8" colSpan=4>Month</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8" colSpan=2>Year</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #a5a5a5" colSpan=4 height=20>Apprentice</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8">Day</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8" colSpan=4>Month</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8" colSpan=2>Year</TD></TR></TBODY></TABLE>


Each class have a check mark box next to them (resulting in a TRUE value in the cell next to the box) and a date of effectivity

All the day, month and year field are drop down menu

Here's a small scenario to illustrate what I need

1- July 27 2011 - User start using the spreadsheet. He's an apprentice at that moment so he check the box next to apprentice and input date July 27 2011
2- August 5 2011 - He becomes a NonCert so he click that box and input the date
3-He's an NonCert for 3 years. Every year at his anniversary date, he gets a new salary AND at every July 1, he gets another salary change (inflation)
4-February 8 2014 He become an ACA and input the date
5-July 1 2014 Goes up a scale in the ACA scale and stay ACA for 2 years
6- November 11 2015 He becomes CrewChief, input the date. As a crewchief, the only salary changes he gets is on the first of july of each year

So the generated list need to look like this (only the dates, not the comments)
July 27 2011____________ apprentice
August 5 2011___________becomes a Non cert
July 1 2012_____________inflation
August 5 2012___________noncert anniversary
July 1 2013______________inflation
August 5 2013___________noncert anniversary
February 8 2014__________becomes ACA
July 1 2014______________inflation
February 8 2015__________ACA anniversary
July 1 2015______________inflation
November 11 2015 ________becomes CrewChief
July 1 2016_______________inflation
July 1 2017_______________inflation
etc.....

I've tried different stuff but everytime, a new problem occurs and it just wont work. The best formula I found was full of IF and it was getting very confusing and I was still very far from the desire result. I'm pretty sure this is gonna end up with VBA but I know nothing about that.

If anybody can help, it's gonna be very appreciated.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,602
Messages
6,179,843
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top