Category: Excel

Hidden Excel: Spreadsheet Compare

Capture2

This hidden gem,  (available with Office Professional 2013,  2016, and Office 365 ProPlus) makes it easy to compare two workbooks, interactively. You can see how values, formulas, and formatting differ – line by line, and cell by cell, highlighting just the type of differences you specify.

To Use Spreadsheet Compare

  1. Find and launch the app by clicking Start then typing ‘Spreadsheet Compare’.
  2. Click Compare Files. The Compare Files dialog appears.
    Capture
  3. Click the folder icon to select the older and newer files, then click OK.

Cheers

quincy

Related Posts

  1. Hidden Excel: Find Date Differences with DATEDIF
  2. Excel Text Functions 1 of 4: =LEFT() and =RIGHT()
  3. Using Histogram for Data Analysis

Happy New Year 2019

 

happy new year message, keyboard enter key

By now, I am likely suffering the ill effects of an Egg Nog induced coma, so in my cognitive absence, I present the most read posts from the year. Enjoy and thanks for reading!

and because nothing cures a New Year’s hangover better than an AskQ post!

Most Read ASKQ Posts: 2018

Google Sheets Macros

macros
Google Sheets users can now record and execute macros. As every Excel user can attest to, this can be a invaluable when it comes to:

  • automate repetitive tasks
  • standardize data entry and data validation
  • execute a complex series of steps

Cheers!

quincy

Related Posts:

Midyear Roundup: My Best Dates

Some of my fave Excel Date Functions. Some old, some new; all designed to help track the passage of time.

Date Functions:

EOMONTH
returns the last day of the month		 EOMONTH ( start_date , month )
EDATE
returns the date x number of months before/after a given date		  EDATE ( start_date , months )
NETWORKDAYS
calculates the number of working days
(see also NETWORKDAYS.INTL)		  NETWORKDAYS ( start_date , end_date , [holidays] )
TODAY
returns the current date		  TODAY( )
WEEKNUM
returns serial number for a given week		  WEEKNUM ( date , [week_start] )
WORKDAY
calculates the next valid work day
(see also WORKDAY.INTL)		 WORKDAY( start_date, days, [holidays] )

 
YEARFRAC
returns the date as a fraction of the year		  YEARFRAC ( start_date , end_date , [basis])

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

Networkdays.INTL

As my international followers have reminded me, not every place sets aside two whole days for the weekend, nor does everyone use the same days of the week.
  • For Afghanistan, Egypt, and Pakistan their weekend starts Thursday at sunset, running from Friday through Saturday.
  • In Malaysia, based upon which part of the country your in, it’s either Saturday thru Sunday, or Friday thru Saturday.
  • Brunei Darussalam‘s non-contiguous weekend is on Friday and Sunday.
  • Costa Rica, North Korea and Uganda get only one day, Sunday, as a weekend.

As a follow-up to last week’s post here’s the international solution to calculating workdays.

NETWORKDAYS.INTL

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.

Syntax

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

  • Start_date and End_date (required). The dates for which the difference is to be computed.
  • Weekend (optional). A number value indicates the days of the week that are weekend days and are not included in the number of working. Below are the values and their corresponding weekdays.
Number Value Weekend days
1 or omitted Saturday, Sunday
2 Sunday, Monday
3 Monday, Tuesday
4 Tuesday, Wednesday
5 Wednesday, Thursday
6 Thursday, Friday
7 Friday, Saturday
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17

Saturday only

Holidays (optional). Indicates the dates that are considered non working days and not counted in the number of working days.

Cheers!

hɔuᴉnb

Additional reading:

 

 

Calculating Total Working Days (NETWORKDAYS)

Calculate the difference between two dates, exempting the weekends and holidays, using =NETWORKDAYS

Using NETWORKDAYS function:

  • Format: =NETWORKDAYS ( date1, date2, holidays)
  • date1 must be the earlier of the two dates.
  • holidays is an optional argument

Cheers!

hɔuᴉnb

Additional reading:

Comments questions (and magik tricks) are always welcome!

Hidden Excel: Find Date Differences with DATEDIF

In honor of the 40th anniversary of J.K. Rowling’s seminal classic, Harry Potter, this post magically exposes a hidden excel function!

Date Difference between J.K. Rowling’s Birthday and Harry Potter Movie Debut

Ever wonder if your spreadsheet is keeping something from you?

DATEDIF is an under-documented function. It allows  you to calculate the difference between two dates. Although it doesn’t appear in the Function Wizard (it exists to insure backward compatibility with Lotus and earlier Excel versions) it is a powerhouse when it comes to calculating date differences.

Using DATEDIF function:

  • Format: =DATEDIF ( date1, date2, “interval”)
  • date1 must be the earlier of the two dates or an #NUM error is returned.

Interval Arguments:

y Years
m Months
d Days
ym Months, years excluded
yd Days, years excluded
md Days, years and months excluded

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

%d bloggers like this: