Seeking Suggestions

[Nanaia]

I'm modifying a template I found online a few years ago because I like the scrolling calendar it uses. I'm trying to get Excel to display the first day New Sick Time is used (a new process being implemented). I've attached a link to a DropBox file of the spreadsheet I'm working on. I'm trying to figure out how to get Excel to display the first day the New Sick Time is used (Code NS) and display that day in column NL for each employee but I'm not familiar with working with scrolling layouts within Excel. Is there a way to get it to display when the first NS day is used within the last 365 days?
I hope this makes sense. Asking about Excel is so dang challenging...
DropBox File

 

[Nanaia]

Is it even possible to pull a "first occurred" date from a scrolling calendar?

 

[NoSparks]

Firstly, your thread title does nothing to entice those that could be interested in calendar questions to look at this.
Secondly, replying to your own thread like that removed it from the Unanswered list and that's where the board regulars go looking for questions to help with.
Sorry but you need to know.


I've looked at the file linked to in this thread and your other post where JamesCanale provided a solution to the question you asked.

I don't think what you asked there is what would be required to satisfy what I think you're looking for here.
My suggestion for this thread would be to use VBA instead of formulas but I'm not really clear on the 365 days part.

Is there a way to get it to display when the first NS day is used within the last 365 days?

Does this mean the actual last 365 days from today back, or does it mean the 365 days of this calendar? (ie: everything starts anew Sept 1, 2022)

Is it reasonable to think that with the NS thing being newly implemented, presumably started September 2021, that there are no last 365 days to be considered so the date going into column NL could be done at the time NS is entered?

Can you tell us what you do for advancing the calendar a year?
I don't mean change the year in A2, I mean things like clearing the contents of columns B:NI for each employee.

 

[Nanaia]

Firstly, your thread title does nothing to entice those that could be interested in calendar questions to look at this.
Secondly, replying to your own thread like that removed it from the Unanswered list and that's where the board regulars go looking for questions to help with.
Sorry but you need to know.


I've looked at the file linked to in this thread and your other post where JamesCanale provided a solution to the question you asked.

I don't think what you asked there is what would be required to satisfy what I think you're looking for here.
My suggestion for this thread would be to use VBA instead of formulas but I'm not really clear on the 365 days part.

Does this mean the actual last 365 days from today back, or does it mean the 365 days of this calendar? (ie: everything starts anew Sept 1, 2022)

Is it reasonable to think that with the NS thing being newly implemented, presumably started September 2021, that there are no last 365 days to be considered so the date going into column NL could be done at the time NS is entered?

Can you tell us what you do for advancing the calendar a year?
I don't mean change the year in A2, I mean things like clearing the contents of columns B:NI for each employee.

Thank you for the information about what replying to my own post does. I didn't know. I was attempting to draw attention to it.

JamesCanale responded to a post related to this, but it was not the same question. The original calendar that I was working with (the file in the DropBox) was set up to display only one month at a time and you advance the month by clicking on the arrows. The month was displayed between the arrows and the date of the month was displayed in a separate cell in each column so the date is not displayed in just one cell. Because I worded the subject line poorly (after realizing what I'd done I tried to correct it but could not) and wasn't getting any feedback from people who know Excel better than I do, I changed the format of the calendar so the month, day, and year were at the top of the columns and it didn't have the arrows to advance the month (I'm sorry but I don't know what that function is called). The file with the changed format is what JamesCanale provided a solution for. I would prefer the format of the spreadsheet in the DropBox but I still don't know how to get it to work when the date is not contained in a single cell.

The New Sick time (NS) is being implemented however the old Sick time (S) still has to be tracked to be reported on until the end of the fiscal year. NS started being used in August 2021 and HR just found out it needs to be tracked differently than they were tracking it (rolling year versus set fiscal/calendar year). The NS is a rolling sick time that does not reset at the end of the calendar year or the fiscal year, but resets one year after the first time it is used. HR needs to be able to track the NS for at least the next year to eighteen months.

Yes, the date in column NL would be automatically filled at the time the first NS is entered.

I anticipate needing to add columns to extend the dates being tracked in the spreadsheet at some point. There is also a possibility that HR will want to create a new file for the new year. It will be based on the information they receive from corporate. They are talking about a new software program being implemented making the spreadsheet obsolete at some future date but need something to track the data in the meantime.

 

[NoSparks]

OK, I played around with your file over the weekend.
I have added a macro to module1 that will put actual dates into row 1 of the scrolling columns.
I use the Worksheet_Change event of the LeaveTracker sheet to put the date into NL as soon as NS in entered,
and also to redo the row 1 dates whenever A1 or A2 are changed.

Have a look at the macros (Alt +F11) will open the VBA environment,
read the few comments I've put in, and just play around with things to verify if it works as expected.
Let me know if you have any questions.

Link to the file I used.

Good luck with the project
NoSparks

 

[Nanaia]

OK, I played around with your file over the weekend.
I have added a macro to module1 that will put actual dates into row 1 of the scrolling columns.
I use the Worksheet_Change event of the LeaveTracker sheet to put the date into NL as soon as NS in entered,
and also to redo the row 1 dates whenever A1 or A2 are changed.

Have a look at the macros (Alt +F11) will open the VBA environment,
read the few comments I've put in, and just play around with things to verify if it works as expected.
Let me know if you have any questions.

Link to the file I used.

Good luck with the project
NoSparks

NoSparks,
My apologies for the delay. I got pulled onto another project at work plus I'm a full time student so time isn't something I have a lot of.

I did a quick test using your Macro change and it seems to work! Thank you! I plan to dedicate more time to learn about Macros (I am definately a novice). So many employers have them disabled and locked by default that I haven't invested much time in learning them for use at work (I use them at home and for school in other programs).
Thank you again. I appreciate the effort you put in to figuring this out!

 

[Nanaia]

OK, I played around with your file over the weekend.
I have added a macro to module1 that will put actual dates into row 1 of the scrolling columns.
I use the Worksheet_Change event of the LeaveTracker sheet to put the date into NL as soon as NS in entered,
and also to redo the row 1 dates whenever A1 or A2 are changed.

Have a look at the macros (Alt +F11) will open the VBA environment,
read the few comments I've put in, and just play around with things to verify if it works as expected.
Let me know if you have any questions.

Link to the file I used.

Good luck with the project
NoSparks

Is there a way to get it to correctly display what week of the year (row #7) using this type of calendar?

 

[NoSparks]

Having real dates in row 1
B7 would be =IF(B$1="","",WEEKNUM(B$1,2))
then dragging from B7 to NI7, which would require unhiding all columns.

Running this macro once will put the formula into all those cells

Sub PutInWeekNums()
LeaveTracker.Range("B7:NI7").FormulaR1C1 = "=IF(R1C="""","""",WEEKNUM(R1C,2))"
End Sub

 

[Nanaia]

Having real dates in row 1
B7 would be =IF(B$1="","",WEEKNUM(B$1,2))
then dragging from B7 to NI7, which would require unhiding all columns.

Running this macro once will put the formula into all those cells

Sub PutInWeekNums()
LeaveTracker.Range("B7:NI7").FormulaR1C1 = "=IF(R1C="""","""",WEEKNUM(R1C,2))"
End Sub

Thank you. I'll test it as soon as possible.