Hello there,
I have not been able to find an easy solution and with limited Excel skills, I am stuck with a problem of creating a business day calendar. This is rather complicated and I hope somebody could assist, if possible.
Just to preface, I have read:
http://forum.chandoo.org/threads/calendar-calculation.13514/
http://chandoo.org/wp/2012/05/03/formula-forensic-020/
http://chandoo.org/forum/threads/calendar-working-day-calculation.13509/
But haven't quite understood how the above might work for me.
Intro:
For work purposes, I am to create a business day calendar that can calculate a business date from a given amount of retrospective (past) and prospective (future) business days. Please note, I do not wish to calculate a duration of days, which I already have, rather, with a provided duration of business days, I wish to ascertain an actual business date in the past or future.
I have a list of holidays, weekends, and business days between 2011 - 2014 already in the provided attachment.
(The calendar calculator spreadsheet should work like this online example, where, given a past or future amount of business days, it will calculate the business date:
http://www.timeanddate.com/date/weekdayadd.html?d1=18&m1=12&y1=2013&type=add&ad=10)
However, our staff do not have internet access and therefore require an Excel solution rather than this online version above. (Also, in order to ensure longevity, I should be able to update the calendar in years 2015 and so on rather than relying on the online variant).
Issue:
Ideally, the calendar should count the following values: 130 business days in the past, 118 business days in the past, 65 business days in the past, 10 business days in the past, and lastly, 10 business days in the future - all from today's date (when today is a business day). Of course, the definition of business days excludes Saturdays, Sundays and public holidays.
If today is not a business day, it should 'look forward' before counting. eg. If today is Saturday, it should go forward to Monday and then provide its count from Monday.
NB:
I have seen the 'networkdays' formula and 'workdays' formula but can't figure out how to apply them to create the above. Lastly, it does not matter if the calendar displays today's BD by default automatically or not in a cell. This entire solution could be like a table/visual database which a user could scroll down a list of dates to find the corresponding -130,-118,-65,-10 and +10 business days by searching for today's date.
This to me has been mind boggling and tricky. Would appreciate any assistance that could be given during this holiday period.
Regards,
OT82
I have not been able to find an easy solution and with limited Excel skills, I am stuck with a problem of creating a business day calendar. This is rather complicated and I hope somebody could assist, if possible.
Just to preface, I have read:
http://forum.chandoo.org/threads/calendar-calculation.13514/
http://chandoo.org/wp/2012/05/03/formula-forensic-020/
http://chandoo.org/forum/threads/calendar-working-day-calculation.13509/
But haven't quite understood how the above might work for me.
Intro:
For work purposes, I am to create a business day calendar that can calculate a business date from a given amount of retrospective (past) and prospective (future) business days. Please note, I do not wish to calculate a duration of days, which I already have, rather, with a provided duration of business days, I wish to ascertain an actual business date in the past or future.
I have a list of holidays, weekends, and business days between 2011 - 2014 already in the provided attachment.
(The calendar calculator spreadsheet should work like this online example, where, given a past or future amount of business days, it will calculate the business date:
http://www.timeanddate.com/date/weekdayadd.html?d1=18&m1=12&y1=2013&type=add&ad=10)
However, our staff do not have internet access and therefore require an Excel solution rather than this online version above. (Also, in order to ensure longevity, I should be able to update the calendar in years 2015 and so on rather than relying on the online variant).
Issue:
Ideally, the calendar should count the following values: 130 business days in the past, 118 business days in the past, 65 business days in the past, 10 business days in the past, and lastly, 10 business days in the future - all from today's date (when today is a business day). Of course, the definition of business days excludes Saturdays, Sundays and public holidays.
If today is not a business day, it should 'look forward' before counting. eg. If today is Saturday, it should go forward to Monday and then provide its count from Monday.
NB:
I have seen the 'networkdays' formula and 'workdays' formula but can't figure out how to apply them to create the above. Lastly, it does not matter if the calendar displays today's BD by default automatically or not in a cell. This entire solution could be like a table/visual database which a user could scroll down a list of dates to find the corresponding -130,-118,-65,-10 and +10 business days by searching for today's date.
This to me has been mind boggling and tricky. Would appreciate any assistance that could be given during this holiday period.
Regards,
OT82
