LinkBack URL
About LinkBacks
I am trying to adapt a calendar within the attendance tracker template.
I need this to show April 2016 - March 2017 and not just individual years 2016, 2017, 2018 and so on
Untitled.jpg
Untitled1.jpg
I am trying to adapt a calendar within the attendance tracker template.
I need this to show April 2016 - March 2017 and not just individual years 2016, 2017, 2018 and so on
Untitled.jpg
Untitled1.jpg
You will need to reorder those rows so the first row is April.
Then when you get to the January row further down, you will adjust your formula to add 1 year:
=DATE($AN$5+1....
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks, but everytime that i delete a row, the row then becomes January and wont stay at March?
Maybe just type the Month names in?
Everything works really well apart from the following;
It counts the number of days that I input into the tracker tab but does not colour the corresponding date between the months of October, November and December
Any help would be much appreciated.
Thank you
The question is unclear. Perhaps if you uploaded a sample workbook and point out where the issue is and how to replicate it.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Re: employee attendance tracker - need to display April 2016 - March 2017 not 2016, 2017..
Image1.jpg
As you can see the number of days sick i 3 yet only 1 box is coloured red, the same as days time owing shows 1 but its not coloured purple anywhere?
this only happens through the months of October, November and December
I've looked at the conditional formatting and i can not see any difference?
Thank you for your help
You have been asked to upload the workbook, not an image of it. Please follow the instructions in post #6 to do so. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
I noticed the 1 sick day in March is for 2017.)
Although the Calendar Year (AN5) is 2016 the dates in the calendar from October to March are Year 2017.
Hence the March sickness being highlighted but not those in October as these dates (in "employee leave tracker" are in 2016.
Same is true for subsequent years.
Last edited by JohnTopley; 10-19-2016 at 04:44 AM.
Thanks JohnTopley at least I know why now, do you know how I can change it?
The honest answer is No without understanding the logic of the "Calendar".
All I can say is that 1st October 2016 is Sunday in the calendar whereas it should be Saturday.
I notice on the first image you posted (if it was year 2016) that 1st October is correct i.e. a Saturday BUT the calendar started in January.
So it suggests moving to a starting month other than January "invalidates" the calculation behind the calendar.
"Pete_UK" is the calendar expert so hopefully he may pick this up.
Thank you, what you have done has helped.
I changed the detail of those months in the tracker tab to 2017 and they appeared straight away, its frustrating but I'm getting near to the end now
I really like this template, we run April to March hence why I wanted to change it and it was beyond my abilities to do so. After searching I found this version which runs April to March so thought I had it sorted
Hopefully "pete_uk" will see this and help me?
Appreciate your assistance
I spotted this in the named ranges dialog. It's the formula that dertermines the start date:
=DATE(IF((ROWS('Calendar View'!$1:1)<7),'Calendar View'!$AN$5,'Calendar View'!$AN$5+1),MONTH('Calendar View'!$C9),1)
and I think it should be:
=DATE(IF((ROWS('Calendar View'!$1:1)<10),'Calendar View'!$AN$5,'Calendar View'!$AN$5+1),MONTH('Calendar View'!$C9),1)
Well spotted Ali - seems to do the job.
Yes, I think it does, John. It makes sure that the year changes after December and not after September!
@Ali,
I checked January and it is 2017.
Not sure I quite grasp the logic (haven't looked too deeply) !
You're a star.
Oh my!!!!!! (Currently doing a little dance)
Amazing!!! thank you so so much "AliGW" and to everyone else of course!!!
I wold suggest that you change the formula in C9 of the Calendar sheet to this:
=DATE($AN$5,4,1)
then in C10 you can have this formula:
=DATE(YEAR(C9),MONTH(C9)+1,1)
and you can copy this down to C20 to ensure that the dates in the calendar start in April and continue successively.
I'm not sure if the other problems have now been sorted out.
Hope this helps.
Pete
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Could you also "Add Reputation" for Ali as she solved the issue.
You're welcome - glad to have helped!Amazing!!! thank you so so much "AliGW" and to everyone else of course!!!
It's just the row count: in the original formula the year was changing from $AN$5 to $AN$5+1 at row 7 of the array (October) instead of row 10 (January), which was making the start day in October a day later than it should have been.Not sure I quite grasp the logic (haven't looked too deeply) !
I think everybody should get some, but I shan't worry if I don't.Could you also "Add Reputation" for Ali as she solved the issue.
EDIT: And I didn't - LOL!!!
Last edited by AliGW; 10-19-2016 at 06:45 AM.
Hi I do not know if this is still active but I am looking to set up as an academic year starting with September ending in August and I cannot follow the steps listed
Can anyone help with a step by step guide to change?
Thank you very much
Please start a new thread (you can reference this one if needed) and I will add a September-August calendar.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
I have posted a new thread thank you - sorry I didn't realise that was what I needed to do
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks