98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Date Calculator
Date Calculator
Resolved · Medium Priority · Version 2007
Mon 9 Aug 2010, 16:01Delegate Martin said...
Martin has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Excel Introduction course
Excel Intermediate course
Excel Advanced course
Date Calculator
How can I use Excel to create a spreadsheet calculator where, if I enter a series of given dates in one column, Excel will enter a date 6 working days prior to the given date, exluding Saturdays, Sundays, UK Bank and Public Holidays + another other dates I wish excluded? THe outcome I'm looking for is 6 working days prior to the given date.
Thanks
Martin
Tue 10 Aug 2010, 11:41Trainer Anthony said...
RE: Date Calculator
Hi Martin, thanks for your query. You need Excel's WORKDAY function. Rather than me duplicate the walkthrough, have a look at this, I think it's pretty much what you're after:
http://office.microsoft.com/en-us/excel-help/workday-HP005209339.aspx
Hope this helps,
Anthony
Wed 11 Aug 2010, 13:08Delegate Martin said...
RE: Date Calculator
Hi Anthony,
Many thanks for the information which does help.
However, I must apologise because having tried the suggestion out, I now realise that as part of my original post, I should have indicated that some of the dates which I need the formula to calculate will be in the past.
Eg the given date could be 01/08/10. Can I use WORKDAY (with some dates to be excluded from the calculation) to return a date however many workings days necessary BEFORE the given date.
Many thanks in advance and apologies for the lack of complete infromation in the first place.
Regards
Martin
Thu 12 Aug 2010, 01:38Trainer Anthony said...
RE: Date Calculator
Hi Martin, that shouldn't be a problem. Just make the "days" argument of the function negative to work backwards.
Hope this helps,
Anthony
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Shortcuts for working with named ranges in ExcelIf you are working with or creating named ranges in your spreadsheets, then you may find the following shortcut keys useful. |
