Getting Results Via Dates

P

psycoperl

Active Member
Joined
Oct 23, 2007
Messages
339
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
I am working on a spreadsheet project to track my work leaves balances based on both current date and projections based on end of our leave budget year.

What I am trying to do is on my Summary Sheet display the balances for 4 different types of leave (Comp Time, Annual Leave, Unscheduled, Sickleave) each of which is listed on its own sheet tab (using the same column layout -- with the exception of Annual Leave which has a FY DAYS MUST USE column) and achieve the following goals:
  1. On the Summary Tab: In Column E (E5, E9, E13, E17) display the current balances from the individual sheets base on todays date. The individual tabs each has additional dates in the list as it is also being used to plan for the rest of the budget year.
  2. On the Summary Tab: In Column G (G5, G9, G13, G17) to show the balance at the end of the leave budget year as listed in Summary!E2
  3. Also need to handle when there would be two entries for the same date as Annual Leave and Sick Leave have automatic increments granted at the end of each month, these dates may also be dates that leave is also used so there would be two entries in the plan.
  4. A bonus goal would be to not require manual resorting of each tab (Comptime Plan, Annual Leave Plan, Unscheduled Plan, Sickleave Plan) when new date records are added.
Are these goals achievable and if so how would I be able to do this?

Leave_Use_Plan_FY2021.xlsx
ABCDEFG
1LEAVE USE PLAN
2Annual Leave Year EndingMonday, August 30, 2021
3
4As of Current Date: Mon. February, 15, 2021Estimated as of AY Ending on Mon, Aug, 30, 2021
5ALAnnual Leave2939569D 06H 55M69D 06H 55M
6MUST USE: 24D 06H 55MMUST USE: 24D 06H 55M
7
8
9UHUnscheduled Leave168004D 00H 00M04D 00H 00M
10
11
12
13SLSick Leave59285141D 01H 05M141D 01H 05M
14
15
16
17CTCompensatory Time459910D 06H 39M10D 06H 39M
SUMMARY
Cell Formulas
RangeFormula
C5C5='ANNUAL LEAVE PLAN'!G200
E4E4=CONCATENATE("As of Current Date: ", TEXT(NOW(),"DDD. MMMM, DD, YYYY"))
E5E5=IF(C5<1,CONCATENATE("NO ",UPPER(B5)," AVAILABLE"),CONCATENATE( TEXT(ROUNDDOWN((C5/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((C5-ROUNDDOWN((C5/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(MOD(C5,60),"00"),"M"))
E6E6=IF(C5>=Setting!C2,CONCATENATE("MUST USE: ", TEXT(ROUNDDOWN(((C5-Setting!C2)/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((C5-Setting!C2-ROUNDDOWN(((C5-Setting!C2)/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(MOD(C5-Setting!C2,60),"00"),"M"),"")
G4G4=CONCATENATE("Estimated as of AY Ending on ",TEXT(E2,"DDD, MMM, DD, YYYY"))
G5G5=IF(C5<1,CONCATENATE("NO ",UPPER(B5)," AVAILABLE"),CONCATENATE( TEXT(ROUNDDOWN((C5/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((C5-ROUNDDOWN((C5/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(MOD(C5,60),"00"),"M"))
G6G6=IF(C5>=18900,CONCATENATE("MUST USE: ", TEXT(ROUNDDOWN(((C5-18900)/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((C5-18900-ROUNDDOWN(((C5-18900)/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(MOD(C5-18900,60),"00"),"M"),"")
C9C9='UNSCHED PLAN'!G6
E9,E17,E13E9=IF(C9<1,CONCATENATE("NO ",UPPER(B9)," AVAILABLE"),CONCATENATE(TEXT(ROUNDDOWN((C9/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((C9-ROUNDDOWN((C9/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(MOD(C9,60),"00"),"M"))
G9,G17,G13G9=IF(C9<1,CONCATENATE("NO ",UPPER(B9)," AVAILABLE"),CONCATENATE(TEXT(ROUNDDOWN((C9/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((C9-ROUNDDOWN((C9/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(MOD(C9,60),"00"),"M"))
C13C13='SICKLEAVE PLAN'!G200
C17C17='COMPTIME PLAN'!G200


Leave_Use_Plan_FY2021.xlsx
ABC
1DaysMinutes
2Annual Leave Cap4518900
3
Setting
Cell Formulas
RangeFormula
C2C2=B2*7*60


Leave_Use_Plan_FY2021.xlsx
ABCDEFH
1SICK LEAVELeave TypeNOTEDHMSICKLEAVE BAL
2Monday, August 31, 2020SLBALANCE12115SL BAL: 121D 01H 05M
3Wednesday, September 30, 2020SLIncrement +1440SL BAL: 122D 05H 45M
4Saturday, October 31, 2020SLIncrement +1440SL BAL: 124D 03H 25M
5Monday, November 30, 2020SLIncrement +1440SL BAL: 126D 01H 05M
6Thursday, December 31, 2020SLIncrement +1440SL BAL: 127D 05H 45M
7Sunday, January 31, 2021SLIncrement +1440SL BAL: 129D 03H 25M
8Sunday, February 28, 2021SLIncrement +1440SL BAL: 131D 01H 05M
9Wednesday, March 31, 2021SLIncrement +1440SL BAL: 132D 05H 45M
SICKLEAVE PLAN
Cell Formulas
RangeFormula
H2:H9H2=CONCATENATE(B2, " BAL: ", TEXT(ROUNDDOWN((G2/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((G2-ROUNDDOWN((G2/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(MOD(G2,60),"00"),"M")
A3:A9A3=EOMONTH(A2,1)


Leave_Use_Plan_FY2021.xlsx
ABCDEFGHI
1ANNUAL LEAVE DateLeave TypeNOTEDHMLEAVE MINUTESAL BALFY DAYS MUST USE
2Monday, August 31, 2020ALBALANCE6965529395AL BAL: 69D 06H 55MMUST USE: 24D 06H 55M
3Wednesday, September 2, 2020ALFY20 SPEND-10028975AL BAL: 68D 06H 55MMUST USE: 68D 06H 55M
4Thursday, September 3, 2020ALFY20 SPEND-10028555AL BAL: 67D 06H 55MMUST USE: 67D 06H 55M
5Friday, September 4, 2020ALFY20 SPEND-10028135AL BAL: 66D 06H 55MMUST USE: 66D 06H 55M
6Tuesday, September 8, 2020ALFY20 SPEND-10027715AL BAL: 65D 06H 55MMUST USE: 65D 06H 55M
7Wednesday, September 9, 2020ALFY20 SPEND-10027295AL BAL: 64D 06H 55MMUST USE: 64D 06H 55M
8Thursday, September 10, 2020ALFY20 SPEND-10026875AL BAL: 63D 06H 55MMUST USE: 63D 06H 55M
9Friday, September 11, 2020ALFY20 SPEND-10026455AL BAL: 62D 06H 55MMUST USE: 62D 06H 55M
10Wednesday, September 30, 2020ALIncrement +203527330AL BAL: 65D 00H 30MMUST USE: 65D 00H 30M
11Tuesday, October 20, 2020ALFY20 SPEND-10026910AL BAL: 64D 00H 30MMUST USE: 64D 00H 30M
12Wednesday, October 21, 2020ALFY20 SPEND-10026490AL BAL: 63D 00H 30MMUST USE: 63D 00H 30M
13Thursday, October 22, 2020ALFY20 SPEND-10026070AL BAL: 62D 00H 30MMUST USE: 62D 00H 30M
14Friday, October 23, 2020ALFY20 SPEND-10025650AL BAL: 61D 00H 30MMUST USE: 61D 00H 30M
15Monday, October 26, 2020ALFY20 SPEND-10025230AL BAL: 60D 00H 30MMUST USE: 60D 00H 30M
16Saturday, October 31, 2020ALIncrement +203526105AL BAL: 62D 01H 05MMUST USE: 62D 01H 05M
17Tuesday, November 10, 2020ALFY20 SPEND-10025685AL BAL: 61D 01H 05MMUST USE: 61D 01H 05M
18Thursday, November 12, 2020ALFY20 SPEND-10025265AL BAL: 60D 01H 05MMUST USE: 60D 01H 05M
19Tuesday, November 17, 2020ALFY20 SPEND-10024845AL BAL: 59D 01H 05MMUST USE: 59D 01H 05M
20Thursday, November 19, 2020ALFY20 SPEND-10024425AL BAL: 58D 01H 05MMUST USE: 58D 01H 05M
21Wednesday, November 25, 2020ALFY20 SPEND-10024005AL BAL: 57D 01H 05MMUST USE: 57D 01H 05M
22Monday, November 30, 2020ALIncrement +203524880AL BAL: 59D 01H 40MMUST USE: 59D 01H 40M
23Thursday, December 10, 2020ALFY20 SPEND-10024460AL BAL: 58D 01H 40MMUST USE: 58D 01H 40M
24Friday, December 11, 2020ALFY20 SPEND-10024040AL BAL: 57D 01H 40MMUST USE: 57D 01H 40M
25Friday, December 18, 2020ALfY20 SPEND-10023620AL BAL: 56D 01H 40MMUST USE: 56D 01H 40M
26Tuesday, December 22, 2020ALFY20 SPEND-10023200AL BAL: 55D 01H 40MMUST USE: 55D 01H 40M
27Wednesday, December 23, 2020ALFY20 SPEND-10022780AL BAL: 54D 01H 40MMUST USE: 54D 01H 40M
28Monday, December 28, 2020ALFY20 SPEND-10022360AL BAL: 53D 01H 40MMUST USE: 53D 01H 40M
29Tuesday, December 29, 2020ALFY20 SPEND-10021940AL BAL: 52D 01H 40MMUST USE: 52D 01H 40M
30Wednesday, December 30, 2020ALFY20 SPEND-10021520AL BAL: 51D 01H 40MMUST USE: 51D 01H 40M
31Thursday, December 31, 2020ALIncrement +203522395AL BAL: 53D 02H 15MMUST USE: 53D 02H 15M
32Sunday, January 31, 2021ALIncrement +203523270AL BAL: 55D 02H 50MMUST USE: 55D 02H 50M
33Sunday, February 28, 2021ALIncrement +203524145AL BAL: 57D 03H 25MMUST USE: 57D 03H 25M
34Wednesday, March 31, 2021ALIncrement +203525020AL BAL: 59D 04H 00MMUST USE: 59D 04H 00M
35Friday, April 30, 2021ALIncrement +203525895AL BAL: 61D 04H 35MMUST USE: 61D 04H 35M
36Monday, May 31, 2021ALIncrement +203526770AL BAL: 63D 05H 10MMUST USE: 63D 05H 10M
37Wednesday, June 30, 2021ALIncrement +203527645AL BAL: 65D 05H 45MMUST USE: 65D 05H 45M
38Saturday, July 31, 2021ALIncrement +203528520AL BAL: 67D 06H 20MMUST USE: 67D 06H 20M
39Tuesday, August 31, 2021ALIncrement +203529395AL BAL: 69D 06H 55MMUST USE: 69D 06H 55M
40AL29395AL BAL: 69D 06H 55MMUST USE: 69D 06H 55M
41AL29395AL BAL: 69D 06H 55MMUST USE: 69D 06H 55M
42AL29395AL BAL: 69D 06H 55MMUST USE: 69D 06H 55M
ANNUAL LEAVE PLAN
Cell Formulas
RangeFormula
G2G2=(D2*7*60)+(E2*60)+F2
H2:H42H2=CONCATENATE(B2, " BAL: ", TEXT(ROUNDDOWN((G2/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((G2-ROUNDDOWN((G2/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(MOD(G2,60),"00"),"M")
I2:I42I2=IF(G2>=Setting!C2,CONCATENATE("MUST USE: ", TEXT(ROUNDDOWN(((G2-Setting!C2)/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((G2-Setting!C2-ROUNDDOWN(((G2-Setting!C2)/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(MOD(G2-Setting!C2,60),"00"),"M"),"")
G3:G42G3=G2+IF(AND(NOT(ISBLANK(D3)),NOT(ISBLANK(E3)),NOT(ISBLANK(F3))),(D3*7*60)+(E3*60)+F3,0)
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
You must log in or register to reply here.

Similar threads

P
  • Solved
Conditional Formatting Based on Date Column
Replies
5
Views
862
Joe4
Joe4
P
  • Solved
Negative Values Not Showing as Negatives
Replies
7
Views
2K
Rick Rothstein
Rick Rothstein
P
  • Question Question
IF and Xlookup not showing expected result
Replies
2
Views
849
PaulyK
P
M
  • Solved
Improving my formulas/approach for date/time analysis
Replies
9
Views
1K
mediumrare
M

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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