Excelling with Microsoft Excel 7: Excel 2010 ( Date formulas and shortcuts)

This is the final post of my Excel 2010 journey  with Alison.  Finally! Although I missed my target timeline of completion but the important thing is that I have completed and passed my test. You can check out the previous post on the “if” function.

Dates and formulas

This is useful when the task involves finding the days between two dates. That sounds straightforward, just subtract one from the other. It gets complicated when you are interested in finding the number of week or work days between two dates.

  1. To find the number of weekdays between two dates (=NETWORKDAYS)
WEEKDAYS

Formula requirements to determine the number of workdays between two dates

In the above scenario, I assumed there were no holidays. Results indicates that there are 261 working days in 2016. This formula natural excludes the weekend ie. Saturday and Sunday.

What of places like Dubai that have Friday and Saturday as their weekends? How can one determine the number of working days?

The formula to use is =NETWORKDAYS.INTL

workdays_dubai

Formula requirements to determine the number of workdays between two dates with custom weekends

Results of the above indicated 260 workdays in 2016 for workers in Dubai.

2. To determine the date after a specific number of workdays from a start date (=WORKDAY) or (=WORKDAY.INTL)

workday formula

The above results in an end date of 2 January 2017

workdays intl formula

The above results in an end date of 30 December 2016.

Excel shortcuts

  • ctrl+shift+$ = currency format
  • ctrl+shift +% = percentage format
  • ctrl+shift+L =filter
  • ctrl+ z = undo
  • ctrl +y = redo
  • ctrl+ o = open
  • ctrl +alt+ v = paste special
  • ctrl+ w = close
  • ctrl +f4 = exit
  • ctrl +shift+ (+) = insert a row or column
  • ctrl+ (-) = delete a row or column
  • shift+ f11= insert new worksheet
  • ctrl +shift+ ! = format number to 2 decimal places
  • ctrl +1 = opens format cells dialogue box
  • alt+ (displayed character) = Access keys  are easy to use and there is no need to memorise it

Hope you picked up something new from my entire Excel 2010 posts. Don’t forget to share your excel tips and tricks with us (E-mail omtsdigest@gmail.com and your tip might just be featured on this site!).

What would you want to study with me this time? Take a vote and that would be my next study topic on my self-learning journey. Best of luck!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s