Author: Gary Henderson

Gary Henderson is currently the Director of IT in an Independent school in the UK.Prior to this he worked as the Head of Learning Technologies working with public and private schools across the Middle East.This includes leading the planning and development of IT within a number of new schools opening in the UAE.As a trained teacher with over 15 years working in education his experience includes UK state secondary schools, further education and higher education, as well as experience of various international schools teaching various curricula. This has led him to present at a number of educational conferences in the UK and Middle East.

Reflections on the Bryanston Edu Summit

I recently attended the Bryanston Education Summit, with this being my third visit for what was the third annual education summit.    As has been the case in previous years the weather smiled on the event.   Having now had a little time to reflect I thought I would share my take away’s from the event.

The need for reflection

Sir Anthony Seldon’s keynote began with a breathing exercise where he encouraged all in attendance to get involved, providing all a moment of mindfulness.  This was a bit of a departure from the normal start to a presentation which might highlight the key questions of the session or the key topics.  The purpose of this activity was to highlight the need to stop and reflect.   Sir Anthony also suggested the need for us to stop and ask “am I being the best I can be?”    This message is one I believe strongly in as our fast and frenetic lives often mean we are focussed on getting things done and checking off tasks from our to-do lists, simply moving from one activity to the next.    In education things can get particularly busy as is evidenced by the continued discussions as to workload.   The issue with this is that we don’t have time to reflect on our core values and on what really matters, on being the best we can be.    Without time to reflect we may be very busy however we may be having little impact or may simply be doing the wrong things.  In order to address this we need to provide ourselves both the permission and time to stop and reflect.   I will admit that finding the time isn’t necessarily easy but we need to prioritise and provide ourselves regular opportunities to reflect.  I also think there is value in doing this to model best practice for the students in our care.   Otherwise all they will see is their teachers rushing from task to task, forever busy, and for them this will shape their view of what is normal.

Trust

The session on leadership by Michael Buchanan included mention of trust and the need for leaders to provide their teams the “permission to be themselves”.    I think this needs to permeate through the culture of a successful school to include formal teams such as departments, but also informal teams and all the way through to how teachers lead the students in their care.

In Alex Beard’s session he referred to the need to try and remove things from teachers where they don’t have an impact on teaching and learning going on to suggest that such time might be used to develop technology skills, understanding of cognitive science and subject expertise.   To me this links to trust in that the most obvious thing to remove, or at least the thing which appears most obvious to me, is any task of an administrative nature which is related to accountability.   If we trust teachers we wouldn’t need as much of the paperwork and data to prove that what needs to be done was being done.

Professional Learning

Cath Scutt’s session focussed on the status of the teaching profession.    She quickly identified her concern with the idea that we need to “raise” the status of the profession in that this creates a “deficit” model.    It suggests that there is something wrong or deficient.   This is similar to the concept of Professional Development which has always for me suggested a deficit;   I have therefore always preferred the term “professional learning”.   For me the key issue here is the need for a culture in education similar to the Japanese term Kaizen, or continual improvement, as mentioned by Alex Beard in his presentation.    We should be seeking to improve, or better learn, not because there is a deficiency, but because we have to if we want to be the best we can be and if we want to enable our students to be the best they can be.

Networking

The session focussing on Hattie’s research into visible learning highlighted the importance of teacher self efficacy to student outcomes and also on the need for “teachers who are learners.”   I believe technology can help with both of these issues.   Take for example twitter.   It allows for discussion and sharing of ideas, for us to question our own practices and ideas.    I think as a tool to both self reflect and also to search out new solutions, twitter is excellent.   It also allows us to stretch beyond our own local context and connect with different educational institutions with differing age ranges, focal areas, internal structures and from different parts of the world.    This can only help us both in being more self aware and in being learners as well as teachers.

Conclusion

The third annual Bryanston education summit was an interesting and useful event.  The above only briefly summarises some of the key points of the pages of notes I found myself coming away with.   I suspect as I have more time to reflect other points will likely surface for me.   One area which I haven’t mentioned for example is the impact of technology on student outcomes.   The provided Hattie data indicates 1:1 laptops only have a minor positive impact on student outcomes however, as was suggested in the session, there is a lot of context to be considered in this.  This is something I will likely discuss in a blog post in the near future.     For now I will conclude that my key take away wasn’t a particular leadership approach or curriculum model or learning model.   The key message I heard from sessions was a need to focus on softer aspects of education, on reflecting, on trusting and on working together to ensure the educational experience we provide is the best it can be.

I enjoyed this years event and now hope to be able to put in practice some of what I have learned.   I look forward to next years Bryanston Education Summit.

Social media is bad.

We have all heard the negative headlines in relation to social media and children however as with most stories there are two sides to the coin, and as much as there are negative implications there are also positive ones.   I therefore thought it was appropriate to share my views on the benefits which our children may find in social media.

We have all read about how social media, and related screen time, impact on the sleep patterns of children, how it may result in greater occurrences of mental health issues, that it reduces students ability to concentrate and that it may reduce achievement levels but what about the other side of things.

We live in a more stressful world than ever before.   When I did my standard grades and the odd O-level I wanted to achieve the best results possible but looking back I don’t feel there was any significant pressure.   I don’t remember discussions of leagues tables or comparisons of countries against other countries or even wide scale coverage of the headline results or subject by subject analysis.    These are all common theses days.   Our children are constantly having the narrative reinforced, that exams will shape their future and that they are therefore of massive importance.   This adds stress but where can students go to share their feelings of stress, to vent, to express and to get support and advice?    They could go to their parents, teachers or other adults but our children often find this difficult due to concerns about being judged or about the resulting impact of sharing.  Sharing with a teacher may result in being “put” in extra lessons or being seen to be “less able” whereas sharing with parents may result in having some of their liberties taken away in order to help them “focus” or “put in more effort”.     They must also consider that adults views on things will be based on their experiences which happened some years in the past and therefore do not fully have a bearing on the current world context and on the environment that the students find themselves in.   Social media provides a better option as students can share with their peers and get advice and support from people going through the same situation, in the same, current, context.   A quick look at social media heading up to A-Level and GCSE results day showed plenty of examples of students expressing their stress and worry over the impending results envelope, and/or text message.    This shows a concerning trend but may also have positive implications in that the students can use social media to vent their concerns and frustrations.   Social media also has plenty examples of students sharing words of support, comfort and advice with one another.

We now live in a world where students movements are more closely controlled and monitored.   Gone are the days of the lone instruction being to be “back before the street lights come on”.   Now parents seek to know where children are.    Parents may also ban students from some locales on the basis of perceived risk.  You also have shops banning groups of youths loitering and in some cases even installing devices to make such loitering painful.     There are less opportunities for our children to be social with each other.   Once again social media steps in.    Social media spans the gap allowing children to be social, to discuss and share their thoughts and feelings, even when the adults in their life and society in general is continuing to further curtail the opportunities they have for being social, for fulfilling a basic human instinct which I suspect is all the stronger in a youths teenage years.

I am not saying social media is all good nor am I willing to accept it is all bad.    In the world we now live in it simply “IS”.   What we therefore have to be mindful of is considering the positives and negatives and doing our best to maximise the positive opportunities while reducing as much as is reasonably possible the negatives.

 

 

Microsoft Forms and Live Spreadsheets

I used to use Google Forms and as such when Microsoft introduced Forms I was very excited about being able to use the Office 365 platform for my form based needs however I quickly found one big limitation.

In google I could create a form which for example gathered budget requests.  I could then make this available to all staff.    I could then create a spreadsheet into which the data went but into which I then started to add my own comments regarding each request indicating whether I felt they should be accepted, declined or needed further discussion.  This spreadsheet was then shared with other budget decision makers for their comments.    As such the budget decision makers were able to make notes on a live spreadsheet which instantly updated with new requests as and when they were submitted.    It sped the whole process up significantly.     The issue was the in Microsoft Forms when you accessed the data in a spreadsheet format this was via download facilities meaning the resulting data spreadsheet did not update as new requests came in, and therefore couldn’t be used to keep running notes.

Thankfully, thanks to twitter and my PLN, I have now found that Microsoft have built the exact functionality I need, it is simply that the process is a little different.

Now I need to create a spreadsheet which can be stored in either OneDrive, Teams or Sharepoint.    The spreadsheet just needs to be blank.     Once the spreadsheet has been created you then need to open it up in Excel Online, the web version of Excel rather than the desktop application.    Easiest method to do this is to visit Office.com in a web browser and open the file from the appropriate OneDrive, Teams or SharePoint app.

Once in Excel Online simply go to Insert > Form and select the New Form option.

You should now be presented with the familiar MS Forms interface where you can build the form you want, adding whatever questions you need.     Once you have your form completed try the Preview option, enter and submit some sample data.    Now revisit your online spreadsheet and the data you have entered should now be visible.   You have now created a live spreadsheet which updates as data is submitted via the form.   You can also easily add your own additional comments to the spreadsheet as needed.

For me Microsoft Forms has now become a much better resource and I am looking forward to sharing the above info with colleagues over the coming week so that they too may start making greater use of Forms.

 

Stream Transcripts (Updated)

It was recently brought to my attention that the transcript files in Steam had changed and therefore the code I previously created for extracting the text from these files no longer works (You can read my original posting and code here).     As such I had another look and updated the code so that it would work with the new format.

The issue was that the new format includes additional lines of data which I needed to strip out plus also supports double and single line groups of text.    It didn’t take too long to write a new macro which would support this new format.

You can see the new Macro code below:

Sub Macro1()

Dim introw As Integer
Dim intcount As Integer

‘Delete first 10 rows
For intcount = 1 To 5
Rows(1).EntireRow.Delete
Next

introw = 1
Do While Cells(introw + 1, 1).Value <> “”

‘ delete the five rows preceeding text
For intcount = 1 To 5
Rows(introw).EntireRow.Delete
Next

‘ deal with blocks of 2 or 1 line of text
If Cells(introw + 3, 1).Value <> “” Then
introw = introw + 2
Else
introw = introw + 1
End If

Loop

End Sub

If using the above take care in the way that WordPress converts the minus ( – ) character in my code to a similar looking character in the above.   As such you may get a syntax error if copying and pasting.  If so just delete and replace the minus with the correct character in your code.  If you have any other issues with the above please let me know.

 

ISBA IT Strategy and Cyber Security Conference

The main conference venue before things began on Wednesday

On Wednesday I had the opportunity to present a session at the ISBA’s IT Strategy and Cyber Security Conference in London.   I had previously volunteered to contribute to the conference and was expecting and had planned for a small breakout session anticipating around 20 people.   On the day upon arriving at the conference I found out that my breakout session would be following Mark Steed’s keynote speech in the main conference venue and therefore with quite a few more than 20 people.

The session very much focused on my thoughts and experiences around cyber security with key messages around the extent of the risk we all face plus the opposing extremes of over confidence in security efforts or a constant need for heavy security measures at the expense of school operational efficiency.    I described my approach as being one of a “healthy” paranoia and of a robust risk assessment and risk recording process.

You can read my slides from the session here.

 

Backups: Do you test?

A little bit of a technology post today:  Backups including redundant solutions are increasingly important in organisations as we seek to keep our IT services up and running for our own internal users and also for external users or clients/customers.   This might be taking backup copies of data to tapes, having a redundant firewall or internet connection or having a cloud-based service available to replicate on-premise services in the event of a disaster.   My concern however is that we can feel better for having these solutions in place happy in the knowledge that we are better off and more protected than if we don’t have them.     The issue is that this sense of additional protection is false.   Just by having a backup solution of one type of another doesn’t mean that it will work when things go wrong.    We also need to be cognisant of the fact that when things do go wrong the result is often one of stress and urgency as we seek to restore services while under pressure from users, business leaders and process owners among others.   We need to adopt a scientific mindset and test the backup solution to make sure it works as intended.    It is much better to test our backup solutions to a timetabled plan than having the first test of a solution being a full blown real life incident where failure of the system could result in difficulties for the organisation.   We also need to bear in mind that just because it works on the day the solution was put in place, or even works today doesn’t mean it will work in a weeks or months’ time, or in a years’ time when we truly need it.    We need to have a robust programme of testing our backup solutions to ensure that they work, that we are aware of how they work and any implications and that those who need to use them are comfortable with their use.   Only by doing this can we be more comfortable in the knowledge that, when something does go wrong, we have a solution in place and are ready to put it to use.

The perfect example of the above, for me, was a recent test of our own backup solutions which included a service which indicated that recovery to a redundant system would be complete in 4 hours plus would be based on data backup taken regularly.    Upon testing the solution we found that the 4 hours recovery period was exceeded due to issues with the backup and the data was 3 days old.   We also found that there were implications for other systems when the test failure occurred.

It might be tempting to look on the above in a wholly negative fashion focussing on why the solution didn’t work however I want to avoid this and intend to focus more on the positive side of things.    We now at least know the solution didn’t perform as anticipated, we know more about the implications of the tested failure area, we are basically now more knowledgeable than we were before the test.    We will therefore now work internally and with the backup solution vendor to arrive at solutions that better meet our needs and are hopefully more robust and reliable.

The moral of the story;  Nothing works until you test it to confirm so test your backup provision and test it often.

Some thoughts on AI in education

A recent post in the TES got me thinking once again about AI in the schools.   The post focused on parents fears about artificial intelligence use in schools stating 77% of parents expressed a concern over a lack of transparency.

Firstly before I get into my views on AI let me first take some issues with the reporting and with the parental perception part of the research.   Looking at the research which you can find here, the question asked of parents focused on the “consequences of the use of AI”.   This feels a little negatively biased to start with.    Under this banner question a serious of sub-questions were asked with the participants asked to respond with either don’t know, fairly concerned/very concerned or not very concerned/not at all concerned.  Again the options hint towards negativity and therefore introduce bias.   And finally the sub question itself in relation to transparency for example focused on concerns relating to a “lack of transparency”, again a negative implication and further negative bias.     It is also worth noting that the survey only had 1225 parents contributing.    I think this falls very short of a sufficient sample to draw any meaningful and generalisable findings.   Despite all of the above the TES decided to pick up and report the findings of “parents’ fear about artificial intelligence in schools” including indicating an “overwhelming majority of parents are concerned”.   I find it somewhat funny that concern of potential bias in relation to AI was reported in an article itself so loaded with its own bias.

So to my views;  I myself have concerns regarding AI use in schools however also see much potential.   Funnily enough the Nesta report to which the TES referred concludes that AI in education “promises much to be excited about.”

Given the negative bias in the TES report lets therefore start with my positive views as to the potential for AI in education.   AI is very good at identifying patterns and divergence from patterns within large data sets.    This makes them ideal for analysing the wealth of school and wider educational data which exists to help educators, those responsible for educational policy and decision making, school leaders and even the teachers themselves.    Now thoughts may instantly jump to achievement data sets resulting from testing, final exams or teacher awarded grading however the opportunities far exceed this area.   Take for example data taken from school Wi-Fi, where students are allowed access, in relation to student movements around the school.   This data might help a school reorganise the school day or restructure the timetable in order to become more efficient and maximise the learning time available.   It might also be used to redesign learning spaces or develop spaces for students to rest, take a break and address their wellbeing.  This is but one example of how AI might be used along with school data.

AI can help direct students to appropriate learning materials using data to identify the areas where students need additional support along with the best support materials to meet these needs.    Some platforms already exist and are exploring this opportunity including Century, a platform which I heard very positive stories regarding when recently speaking to students at a school using it.   Platforms like this might prove highly valuable additional resources to complement classroom teaching or to provide a more effective homework platform.   This area and use of AI is likely to continue grow with the development of more and more online learning content being key to this.

AI can help with teacher administrative tasks such as registration conducted via facial recognition or marking of tests by natural language AIs that can apply a given marking criteria to student submitted work.    We also need to recognise some of the AIs that are already available including voice recognition and dictation, which is now a feature of the MS office products.    Googles search facilities, a now standard feature used in schools and classrooms, also quietly uses AI yet we don’t bat much of an eyelid to it.

The negatives implications which exist in relation to AI generally apply beyond the educational context, albeit the educational context in teaching our future generations makes things all the more worrying.

AIs need to be taught and to learn with this done using training data sets.   The worry is that bias in the training data set will result in bias in the AIs decision making.    As a result an AI which was developed in the UK, and therefore trained using UK based data, and used successfully in UK schools may not be appropriate for use in schools in Asia or the Middle East due to its decision making being biased towards a UK context.   That said, this same issue would impact on any product or service, or even individuals where they seek to operate outside their normal context.   We all have an inherent bias, we “humans”, create the AIs and train the AIs so is it realistic to expect an AI without bias?  I suspect part of the issue is a concern in relation to a particular bias being introduced purposefully however I think it is more likely bias in AIs will arise accidentally as it general does within humans.

There is a concern that AI decision making based on large data sets may become impossible for humans to explain or understand, as the decision making process could be based on huge amounts of data.     This brings with it the concern that we may lose some of our control.   If a teacher recommends a career track for a student they will be able to explain how they arrived at this however if an AI was used, the teacher may be able to present the AIs findings but may be unable to explain or understand how this was arrived at.   How many parents who be happy with a suggested career path for their child without any explanation available?

Linked to the above is a concern of “determinism” where AI might identify an end point and then through its actions lead to this occurring.  So those students identified as achieving a C grade in GCSE might be presented with content and learning materials which lead them to achieve exactly that.  This concern is again about a lack of control however it could be suggested we are deterministic in some of the practices already in use widely in schools.   Take for example the setting of students into ability bands, is this not potentially deterministic as the students in the top band get the most challenging content which may enable them to achieve top grades while the students in the lowest band gets easier materials which means the don’t learn the more complex materials, and as a result are unable to achieve the top grades.    Also is there a danger of determinism every time a teacher reports a predicted grade to parents or where a school uses ALIS or other benchmarking data?

Overall AI is going to find increasing uses in schools.   My gut feeling however is that for the foreseeable future this will be very much in a subtle way as data analysis systems start to suggest areas to investigate within school data, accessibility tools including dictation and translation support students in class and AI driven learning platforms provide personalised learning opportunities beyond the classroom.   These are but a few examples of things already happening now.  These uses of AI are likely to become more common.   Discussion of AI reminds of a quote in relation to effective technology integration being such that the teacher and learners don’t even stop to think about the fact they are using tech, the tech use is transparent.   I think AI use is going to be exactly this, and the AI in Googles search goes some way to provide this;  When was the last time when you were conducting an online search that you stopped to think about how google search works and how AI may be involved?

 

 

 

 

iPADS helping students manage distraction?

We have all read the various media reports in relation to concerns about children’s screen time, use of social media and also how technology can be distracting and negatively impact the ability for children to focus and concentrate.    I have never really signed up to these concerns, although I have always accepted that at extreme levels of screen time and use negative consequences are likely, that however can be said for most things in life, that an over indulgence will have negative results whether it is over eating, over exercising, over dieting or over use of technology.

A recent visit to a school however casts this whole subject into another light and highlighted a potential benefit I hadn’t really considered.     I was talking with students about the apps they use in school and the group of boys I was talking to where confidently and excitedly describing various apps which they used in different subjects.   It was then that one student turned his iPad so I could see it and pronounced that he used HeadSpace.     He apparently found he got stressed or distracted at times and that the HeadSpace app on his iPad allowed him to take time out and refocus.   Here we had a student using technology to help with focus and distraction.

For those that aren’t aware of HeadSpace, it is mindfulness and meditating app which, according to their website is “a personal meditation guide, right in your pocket”.   As it happens I have myself experimented with HeadSpace so when the student mentioned it, I was aware of what it was.

Technology is in my eyes a tool.   In my eyes it is a very versatile tool or even a “swiss army knife” of educational tools.    Like any tool, it is the use to which we put it that has either positive or negative results as opposed to the tool itself.    Put to the use as a tool to quiet children, keep them blindly busy or simply quiet then it will be no wonder that the outcomes will be negative, that students may find focusing difficult.   Alternatively, like with the pupil I met, it could also be used to tackle the specific negative outcomes that the above poor use might result in.   It could be used to positively support students in managing distraction and in focusing.

Would be interested in if any others are using Headspace or other apps in relation to Mindfulness in students.

BETT 2019

Am sat on the train as I write this returning home from BETT 2019.   Last year I didn’t manage to attend however this year I made an effort and decided to make my way down to the London ExCel on the Saturday.   So what were my impressions and thoughts?

Firstly, I must note it was a bit of a whistle stop tour due to a cancelled train which apparently had sustained damage to its windscreen leading to it being cancelled.   As such I had to wait for a later train and re-plan my journey.   Secondly, I was on this occasion travelling directly to the event via train, the underground and the DLR.   This was the first time I had taken this travel route to BETT and I sadly underestimated the amount of time it would take to make my way from Paddington station over to the Excel, leading to things being a bit rushed as I tried to make my way back for my return train ride home.

As to the conference itself the highlight was a discussion with people at Microsoft in relation to the Surface line of devices.    It was good to sit down and chat about the products and their potential for use in education.    The device I write this post on is my Surface Go which I continue to use as my main device in testing how it might stand up in prolonged use.   So far so good is my view but I hope to know more as we trial with a number of enthusiastic teachers as part of a pilot.    During the meeting with Microsoft I was introduced to their new stylus which is designed specifically for education, apparently being more robust as well as cheaper.    The reduction in cost when compared with the main stylus is a welcome one as it will make the overall Surface Go platform including keyboard and stylus more affordable for schools.   Personally, I think you need the full package of stylus and keyboard/case to make best use of the device.   This new stylus comes with a loop attachment at the end so it can be hung from your neck to prevent loss.   The loop means the eraser option is no longer at the back of the stylus but is now a button on the body.    I liked having erase as a button on the back of the stylus as it fitted my mental model of HB pencils with an eraser on the end, however I am unsure the children we currently teach have the same exposure to HB pencils and therefore although this worked well for me the change to a button on the body may make no difference to them.   I didn’t ask if it was still magnetic like its more expensive brother, however I suspect the answer will be no.  That said I don’t think this would be a deal breaker.     Overall it was a useful meeting to discuss thoughts around Microsoft, the surface and office 365 in particular.

It was nice upon arriving to bump briefly into Mark Anderson just outside the ExCel along with Al Kinglsey.    Due to being in a bit of rush to make my Microsoft meeting I was unable to spend any real time with Mark and Al, which is a pity given I hadn’t actually met Al previously in real life, knowing him only via twitter.    Hopefully I will have time to catch up with both of them properly in the near future.

I am going to jump once more on the number of interactive panel vendors again as again there were lots of them.   Now I know someone previously commented that BETT is an educational technology show so it should be expected to see panels on show, however given the increasing pace of technology I just keep hoping to see more new and interesting technologies and less IWBs or equivalents.     I will however acknowledge that I found myself looking at interactive panels on this occasion as we seek to review our classroom technologies, so if I was looking at IWB equivalents, others must be doing the same so there clearly is a market and therefore an opportunity for those vendors showing of interactive projectors and panels.  I will also acknowledge I continue to have a bit of an issue with IWBs and therefore I am primed and more sensitive to spotting them wherever they are.    I will this time say I did see some interesting stands, beyond IWBs however with the rushed nature of things I didn’t have sufficient time to really explore them.   Will need to bear this in mind for next year.

Cyber security was a little bit of a discussion I had with a school management system vendor which plan to move towards forcing Multi-Factor Authentication on their users.   On one hand this will be a bit of a shock for some and will be met with cries of inconvenience however on another hand I can see exactly where they are coming from.   Schools are being actively targeted due to the large amounts of student and parent data they hold, combined with the busy nature of teachers daily lives, which often lead to simple passwords.     In addition, we have breaches of the likes of Edmodo resulting in significant sets of teacher credentials being available online.   I myself tried a schools name against the HaveIBeenPwned password checker and found hundreds of instances of the use of this as a password, which was subsequently involved in a data breach.   I suspect similarly easily predicted passwords will be in use in schools the world over.

My visit to Bett also saw me visit the ANME stand and catch up with Rick who I had worked with some years ago.    It was good to catch up and I will definitely be getting my team involved in ANME.   I was particularly interested by the start up of a group focusing on the data management side of things.   This fits with our current exploration of PowerBI as a solution to making data more accessible and easily analysed and presented such that school leaders and teachers can make informed and data driven decisions.   As such we will definitely looking to get involved in discussion and sharing of ideas around data management.

I also had a brief discussion with Adobe in relation to licensing following some worried posts I picked up on an Educational IT forum.    The worries lay around a move by Adobe from device licensing towards user based licensing.   This would for some result in significant cost implications.   Thankfully the rep I spoke to told me that licensing would remain the same, but would be a shared device licensing scheme meaning login details would need to be set up for all students using the Adobe applications.    Apparently Adobe are looking at the provisioning side of this including single sign on to try and make the end user experience in this changed model work more seamlessly with this due to be addressed around April this year.   We also had some discussion as to how schools might be offered more flexibility to have some shared device licensing and some user based licensing.   This might be useful for students studying A-Level or BTec Art subjects in that the license would allow the students to install the software on home machines.

And so BETT 2019 has come and gone.    It was an early start to get to BETT by train but worthwhile.    Here’s looking forward to BETT 2020 and hopefully making it a less rushed experience.

See you all in a years time!!!

An Excel calendar

Was asked the other day to create a spreadsheet for staff to record the leave on.    Sounded simple but as with most IT project scope creep made an appearance and the simple specification became a bit more complex.

Apparently the sheet didn’t allow the patterns of leave, etc to be easily viewed across the year.   This was due to having the date as the header and also having to have sufficient space for staff to enter their leave info.     Seemed simple enough to solve just by adding a second sheet with colour coding, referencing the first sheet.   This would allow for each column to be thinner thereby allowing for a larger date range to be viewable.   Job done?

Apparently not, the next addition to the spec was the need to be able to pick out a week and view just that week.   Now this was going to put my Excel skills, and my google searching skills to the test.

The answer lay in a couple of formula which I hadn’t used before:

Indirect

This formula allows me to retrieve the contents of another cell based on a cell reference.

=INDIRECT(Sheet1&P7)

The above retrieves the contents of a cell from Sheet 1 of my workbook, with cell P7 containing a reference.  E.g. P7 might contain !A1 leading to INDIRECT(Sheet!A1) would would therefore retrieve the contents of cell A1 on Sheet 1.

So using this I would make my View Week sheet retrieve the appropriate weeks data from the Master sheet by basically building the appropriate cell reference.   So if Monday of Week one was in column B, we know week two would be 7 letters further on.

Substitute

For the above to work I need to be able to work out the row letter based on a week number.    The Substitute function allows me to convert a number to an equivalent letter.

=SUBSTITUTE(ADDRESS(1,($E$2-1)*7+1,4),”1″,””)

The above takes a week number in cell E2 and from this works out which column the Monday for that week can be found in.   Note: the above dosents work for Week1 as part of the week was in 2018 rather than 2019.  This could be easily fixed.

Text

The final function I needed to use was the Text function.   Reason I needed this is I wanted to create a pull down list showing the week number and week commencing date.   The issue is I had both pieces of data in different fields and concatenating a date doesn’t work; it shows you the number equivalent of the date.      The Text function allowed me to convert the date into a string which could then be included in a concatenate function.

TEXT(B6,”dd-mm-yyyy”)

The above takes the date value in cell B6 and converts to a string of the date.   The above was then placed inside a concatenate function to combine with the week number as below:

=CONCATENATE(“Wk”,A6, ” – “, TEXT(B6,”dd-mm-yyyy”))

The above takes the week number in cell A6 and the date in B6 and combines together ready for display in a pull down list.

I must admit it took a little bit of thinking and a little but of work to get this spreadsheet working however I did enjoy trying to hack together a solution to this problem.     You can access the final calendar here.  I have unlocked all sheets for your editing, plus have made the working cells visible where in the final version I set the font colour as white to make it invisible.