Newbe help with building a SIMPLE & Short Excel Spreadsheet.

RLD123

New Member
Joined
Aug 21, 2016
Messages
6
Hello,

I'm hoping some one can get me started building this excel spreadsheet... I have only a tiny bit of excel experience

I think this spreadsheet should be real simple and only have a few calculations...

I'm thinking the only Data needed will be:

Current Date.

Current Vacation hours.. (I will input this value each time I run the spreadsheet.... I'll get this biweekly updated number from my employer via my pay-stub)

Number of Floating Holiday hours... (again I will supply and enter this number with each running of the spreadsheet.

I get an extra 40 hours of vacation once a year on my anniversary date .. I don't want to make this spreadsheet more complicated that it needs to be.. That additional 40 hours will show up on my pay-stub in a lump sum amount.... SO, If I need to run the spreadsheet right around the time of my anniversary I can just add in 40 hours to my current total vacation hour balance.

For the last several years I've just been calculating it out (the old fashion way) buy using the formula below.

STEP A: 246.5 (My current Vac hours balance) – 160 = 86.5 ……….86.5 / 11.5 = ~7.52 ... (approximate amount of days off available)

STEP B: 42.5 (My Floating Holiday hours balance) ………………42.5/ 11.5 = ~ 3.69...(approximate amount of days off available)
STEP C: Approx. 6.7 Hours per Month. Remaining Months in 2016 = 4……4 X 6.7 = ~26.8 26.8 / 11.5 = ~ 2.33...(approximate amount of days off available)


STEP C: Add A+B+C.. = How many "Days" of vacation I can schedule for the remainder of the year. = 7.52 + 3.69 + 2.33 = ~ 13 Vacation Days I have remaining in the year.

FYI... Anniversary hours = 40 40/11.5 = 3.5... (already included in Vac hours) Annaversary date is April 07

*********

**I always start by subtracting 160 hours from my Vac balance. I like to always have at least 160 hours in the GREEN... any hours in excess of 160 I consider to be available for use during the year. I divide each block of hours.. (Vacation or Holiday) by 11.5 since I work 11.5 hours shifts... and each time I take a full day off... it is charged as 11.5 hours = One day off.

I accrue roughly 6.7 hours of Vacation time per month... I use this value to calculate a look ahead of how many hours I will accrue for how ever many months remain in the year.

If anyone can supply any suggestions towards building this ......Your help will be greatly appreciated.

Thank You. Richard. :eek:
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,244
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
see if this fit your requirements
green cells are input that can be amended to suit, I've assumed Annaversary date is 1st April 07
blues cells are formulas

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style="text-align: right;background-color: #E2EFDA;;">25/08/2016</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Annaversary date</td><td style="text-align: right;background-color: #E2EFDA;;">01/04/2017</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Entitled</td><td style="text-align: right;background-color: #E2EFDA;;">246.5</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Reserved</td><td style="text-align: right;background-color: #E2EFDA;;">160</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Balanced</td><td style="text-align: right;background-color: #E2EFDA;;">42.5</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Accrue Hrs/month</td><td style="text-align: right;background-color: #E2EFDA;;">6.7</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Hrs/day</td><td style="text-align: right;background-color: #E2EFDA;;">11.5</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">A) </td><td style="text-align: right;background-color: #D9E1F2;;">7.5</td><td style=";">days</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">B)</td><td style="text-align: right;background-color: #D9E1F2;;">3.7</td><td style=";">days</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">C)</td><td style="text-align: right;background-color: #D9E1F2;;">4.1</td><td style=";">days</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Total</td><td style="text-align: right;background-color: #D9E1F2;;">15.3</td><td style=";">days</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=(<font color="Blue">B3-B4</font>)/B7</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=B5/B7</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B11</th><td style="text-align:left">=DATEDIF(<font color="Blue">B1,B2,"m"</font>)*B6/B7</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B12</th><td style="text-align:left">=SUM(<font color="Blue">B9:B11</font>)</td></tr></tbody></table></td></tr></table><br />
 

RLD123

New Member
Joined
Aug 21, 2016
Messages
6
see if this fit your requirements
green cells are input that can be amended to suit, I've assumed Annaversary date is 1st April 07
blues cells are formulas

Excel 2012
ABC
1Date25/08/2016
2Annaversary date01/04/2017
3Entitled246.5Hrs
4Reserved160Hrs
5Balanced42.5Hrs
6Accrue Hrs/month6.7Hrs
7Hrs/day11.5Hrs
8
9A)7.5days
10B)3.7days
11C)4.1days
12Total15.3days

<tbody>
</tbody>
Sheet7

Worksheet Formulas
CellFormula
B9=(B3-B4)/B7
B10=B5/B7
B11=DATEDIF(B1,B2,"m")*B6/B7
B12=SUM(B9:B11)

<tbody>
</tbody>

<tbody>
</tbody>

Thank You VERY MUCH AlanY.

I scraped my worksheet and entered the fields you suggested....

It is mostly working now.. (I think I may have field out of alignment or something of that nature) since I am getting two results errors...

I am DEFIANTLY much further along now with your Help.

Like I said, I am a newbie...and a newbie to this site.. so, I do not know if I can send/copy an excel sheet via this forum?


A)7.52173913days
B)3.695652174days
C)#VALUE!days
Total#VALUE!days

<tbody>
</tbody>
 

RLD123

New Member
Joined
Aug 21, 2016
Messages
6
Hello, It looks like the only block I still need to work on is B11. I need a formula that will calculate the remaining amount of months in the year. (It would be nice it it would show partial months.. such as 4.5 months remaining...

I've tried this foumula but it seems to just show whole numbers... =12-MONTH(NOW())

Anyway, back to the formula... I need B11 to determine the remaining amount of months in the year then multiply that number by 6.7 and then divide that result by 11.5

So, in today's case. if I enter the date August 25, 2016... the formula should come back with something like 4 months remaining in the year... (4 X 6.7) / 11.5 = 2.33 Days.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,244
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

the formula in B11, =DATEDIF(B1,B2,"m")*B6/B7 is to work out the different in months between the 2 dates in B1 & B2 then times B6 (6.7) and divided by B7 (11.5).

all you need to do is to put 31/12/2016 in B2 to get your answer

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style="text-align: right;background-color: #E2EFDA;;">25/08/2016</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Year end</td><td style="text-align: right;background-color: #E2EFDA;;">31/12/2016</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Entitled</td><td style="text-align: right;background-color: #E2EFDA;;">246.5</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Reserved</td><td style="text-align: right;background-color: #E2EFDA;;">160</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Balanced</td><td style="text-align: right;background-color: #E2EFDA;;">42.5</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Accrue Hrs/month</td><td style="text-align: right;background-color: #E2EFDA;;">6.7</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Hrs/day</td><td style="text-align: right;background-color: #E2EFDA;;">11.5</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">A)</td><td style="text-align: right;background-color: #D9E1F2;;">7.52</td><td style=";">days</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">B)</td><td style="text-align: right;background-color: #D9E1F2;;">3.70</td><td style=";">days</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">C)</td><td style="text-align: right;background-color: #D9E1F2;;">2.33</td><td style=";">days</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Total</td><td style="text-align: right;background-color: #D9E1F2;;">13.55</td><td style=";">days</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=(<font color="Blue">B3-B4</font>)/B7</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=B5/B7</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B11</th><td style="text-align:left">=DATEDIF(<font color="Blue">B1,B2,"m"</font>)*B6/B7</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B12</th><td style="text-align:left">=SUM(<font color="Blue">B9:B11</font>)</td></tr></tbody></table></td></tr></table><br />
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,244
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
modified for your needs

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style="text-align: right;background-color: #E2EFDA;;">25/08/2016</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Year end</td><td style="text-align: right;background-color: #E2EFDA;;">31/12/2016</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Months remaining</td><td style="text-align: right;background-color: #D9E1F2;;">4.27</td><td style=";">Months</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Entitled</td><td style="text-align: right;background-color: #E2EFDA;;">246.5</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Reserved</td><td style="text-align: right;background-color: #E2EFDA;;">160</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Balanced</td><td style="text-align: right;background-color: #E2EFDA;;">42.5</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Accrue Hrs/month</td><td style="text-align: right;background-color: #E2EFDA;;">6.7</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Hrs/day</td><td style="text-align: right;background-color: #E2EFDA;;">11.5</td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">A)</td><td style="text-align: right;background-color: #D9E1F2;;">7.52</td><td style=";">days</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">B)</td><td style="text-align: right;background-color: #D9E1F2;;">3.70</td><td style=";">days</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">C)</td><td style="text-align: right;background-color: #D9E1F2;;">2.49</td><td style=";">days</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Total</td><td style="text-align: right;background-color: #D9E1F2;;">13.70</td><td style=";">days</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=DATEDIF(<font color="Blue">B1,B2,"d"</font>)/30</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=(<font color="Blue">B4-B5</font>)/B8</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B11</th><td style="text-align:left">=B6/B8</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B12</th><td style="text-align:left">=B3*B7/B8</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B13</th><td style="text-align:left">=SUM(<font color="Blue">B10:B12</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

RLD123

New Member
Joined
Aug 21, 2016
Messages
6
THANK YOU AlanY

YOU ROCK...!!! This spreadsheet is working 100% now... Thanks to you.

Rick.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,244
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Forum statistics

Threads
1,143,658
Messages
5,720,128
Members
422,267
Latest member
olund

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
Top