Categories

## DATEDIF How to Calculate Difference Between 2 Dates

The `DATEDIF` function calculates the number of periods between two dates. The best way to remember this function is it calculates the DATE DIFference between two dates.

## What is DATEDIF?

The `DATEDIF` function is a popular formula that has 3 parameters which are all required. If you [encounter any error]({{< relref “#datedif-errors-and-problems” >}}) when using this function check you have populated all three fields correctly.

The function reference is as follows:

``=DATEDIF(start_date, end_date, unit) ``

The `start_date` parameter should be the earliest date, the `end_date` being the latest date, and the `unit` is the value of the periods you want returned from the difference between those two dates.

Accepted values in Google Sheets for the `unit` value are:

### Date Difference in Days

By setting the third parameter to the `DATEDIF` formula to `D` it means the resulting value returned will calculate the number of days between the two dates. Here are some examples:

Several things to note about these tests when using the `DATEDIF` function and the `unit` parameter as `D`:

1. The dates are not inclusive. Notice the first row is 0, if it were inclusive it would have been 1.
2. Should the `end_date` come before the `start_date` you will get an `#NUM!` error. Make sure your `end_date` occurs on or after the `start_date`.

### Date Difference in Months

If you want to calculate the difference between two dates in months, then set the third parameter of the formula to `"M"`, here are some interesting examples of what this looks like for certain values:

As can be seen from the table above, the day of the month needs to be either eclipsed or matched for the returning value to increment. In rows 5 and 6 especially we can see the days in the month of February 2020 would not have eclipsed the `start_date` of 31st January 2020, but when the `end_date` advances into the next month (1st March) the increment occurs.

Therefore, if your `start_date` is the end of a month containing 31 days it will not be until the 1st day of the following month, preceding a month containing only 28, 29 or 30 days, that the value will be incremented.

### Date Difference in Years

If you want to calculate the difference between two dates as years then set the third parameter value to `"Y"`. Here is what this would look like based on the following inputs:

As can be seen from the examples above, it’s not until the date and month of the `start_date` has been eclipsed or matched the value returned by the `DATEDIF` formula increments. This is very similar to when calculating the [date difference in months]({{< relref “#date-difference-in-months” >}}).

### Date Difference in Days Irrespective of Months

If you need to calculate the difference in days between two dates irrespective of the difference between months then you’ll need to use `"MD"` as the value of the third parameter, as demonstrated below:

As previously noted with the [difference in days]({{< relref “#date-difference-in-days” >}}) we can see from the examples above the difference in days is not inclusive. However, there are some interesting results:

Row 3 shows a result of -1. Why? This implies there was a day short in the previous month of getting to the same date, and the current date of 1st March 2020 is 1 day short of the 31 days (29 days in February + 1 day in March = 30 days – 31 days = -1). This then puts the remaining values of that month (in March) out, until we hit the new month April.

### Date Difference in Whole Months

If you want to calculate the difference between two dates in whole months you can set the third parameter to the `DATEDIF` formula to `"YM"`.

The results from this result set above are fairly simple to understand and are consistent with what we already know about – the day of the month needs to be matched or eclipsed to increment the value.

### Date Difference in Days Irrespective of Years

Similar to our previous example with [date differences in days irrespective of months]({{< relref “#date-differences-in-days-irrespective-of-months” >}}) we have a similar calculation but this time irrespective of years, here are some examples:

Consistent with the days difference irrespective of months we see that where the start date isn’t matched by day of month and month, it calculates the difference the previous month fell short.

## Benefits of DATEDIF Over Subtracting Two Dates

An objection to using the `DATEDIF` formula is that it achieves the same result when subtracting dates, which is certainly far simpler than trying to remember a formula, and it’s parameter values.

The biggest benefit of the `DATEDIF` formula is that it helps to calculate the difference between two dates by providing us with the type difference we need. Therefore, the `DATEDIF` formula is more versatile for various use cases.

Another minor benefit to using the `DATEDIF` function when calculating the difference in days between two dates over the simple subtraction method is if the input dates contain time.

You can still achieve the same answer as the `DATEDIF` function, but you would need to remember and apply to use the `TRUNC` formula to achieve the same answer in row 3 above, like so:

## DATEDIF Errors and Problems

There are a couple of things to be mindful of when using the `DATEDIF` function. As already shown above in the [DATEDIF days examples]({{< relref “#date-difference-in-days” >}}) we had a result on the last row that gave us an error.

If you do get a `#NUM!` error check the parameter values are correct, and the earliest date is the `start_date` value, and the later date is the `end_date` value.

Provided your parameter values have been set correctly, the only other type of error which may cause problems and may not be as obvious is if the locale setting of your dates are not what you anticipated.

### Incorrect DATEDIF Locale

If you’re getting an answer which definitely isn’t correct you will want to check the settings of your spreadsheet to ensure it correctly interprets a date field value.

For example, some countries, such as the UK and Australia, have dates set to the following format: `DD/MM/YYYY` whereas other countries, such as Canada and the Unites States, have date values set to the format of: `MM/DD/YYYY`.

Ways to minimise this error occurring would be to quickly check your locale settings are correct before starting a new spreadsheet, or when modifying a spreadsheet.

In a Google Sheet this would be simply going to File > Spreadsheet Settings > Locale – change this to your country.

{{< figure src=”/images/datedif-check-locale.png” caption=”Check spreadsheet locale settings” alt=”Google Sheets locale setting” >}}

Another way to check is to input a date that wouldn’t be a date in the other format, for example, try `01/13/2020` and `13/01/2020` – one of these will not work and your `DATEDIF` formula will return a `#VALUE!` error as the date fields entered aren’t actual dates.

## Conclusion

In this article we’ve done a deep dive into the `DATEDIF` formula and seen the results it provided based on different parameter values.

We’ve also seen how the formula can be better suited than simply subtracting two dates, and we’ve diagnosed our problems and fixed them.

Overall the `DATEDIF` function is a fantastic function to use, and you should look at incorporating it more in your own daily use. If you’d like to learn more about the function in Google Sheets, you can read the [documentation]({{% relref “datedif-docs” %}}).

Categories

## What is the Best Set Up For SuiteScript Coding in Webstorm?

As mentioned in my previous post on what my preferred text editor is for SuiteScripting NetSuite does provide a deeper integration with JetBrains’ WebStorm product through a plugin.

To enable this plugin in WebStorm you will need to access this help page in NetSuite’s documentation and follow the step by step guide.

(The process is fairly straightforward, but unfortunately the plugin doesn’t play well with PyCharm.)

Once you then have the plugin installed in WebStorm you then need to ensure you undertake (or already have done) the following steps:

1. Set up the SuiteCloud Development Framework
2. Create the SDF role (if needed) – I’m our account administrator, so I just use this role.
3. Fetch a token.
4. Create new “Account Customization” project in WebStorm.
5. Set the Master Password for this project by clicking on the NetSuite > Master Password and setting it accordingly.
6. Then click on NetSuite > Manage Accounts and follow through the prompts to get yourself connected.
7. Insert your token ID and secret into the user account.
8. Now time to download your project folder from within your File Cabinet. Just right-click on the folder in the Project Explorer labelled FileCabinet and then hover over the NetSuite icon on the dropdown menu and then select “Import Files from Account…”

Then you will have the option of selecting what project folder you would like to play around with.

From here on out it’s just a simple case of right-clicking on files and hovering over the NetSuite icon and selecting what you’d like to do.

1. Deploy
2. Validate

You might even want to make it a short-cut code on your keyboard!

Categories

## Best Editor For Coding SuiteScript Seamlessly

If you’re looking for a good text editor to perform your SuiteScript coding there are good editors, such as Microsoft’s Visual Studio Code, but the one I personally use on a regular basis is PyCharm.

Both platforms enable you to:

• Write and analyse your JavaScript code;
• Have integrated terminal and console windows;
• Git integration;
• Ability to code in other languages, such as Python (great for web-scraping and data analysis of your saved search csv files!); and
• Connection to your SuiteScript File Cabinet (although the plugin for VSCode is a little more cumbersome to set up), and once setup you can then upload directly to your project’s folder in NetSuite.
• Both are free PyCharm Community Edition, although I have an Ultimate licence that provides access to all of JetBrains products).

PyCharm and the more commonly known other JetBrains IDE WebStorm have been around for quite some time, and I have been more familiar with their product because of this.

As a result I use more frequently the PyCharm IDE as my go to editor and occasionally when I’m coding in the Advanced Template syntax (FreeMarker) I will use the [IntelliJ](https://www.jetbrains.com/idea/ editor).

I have requested for this to be a plugin so that I don’t need to pay for all different versions of JetBrains, so if you similarly inclined you may want to up-vote this request too

I’ll detail more in subsequent posts about how I use the PyCharm IDE from JetBrains in my day to day workflow.

Categories

## How To Fix “Invalid Property Assignment (Error 450)”

If you are testing the return value of a function in the immediate window in VBA and get the following error:

Wrong number of arguments or invalid property assignment (Error 450)

What you are doing is something like this:

``````Function GetCollection() As Collection
Dim coll As New Collection
Set GetCollection = coll
End Function``````

Then in the immediate window typing:

``? GetCollection()``

The code appears to work fine, but the error is a mystery. The reason for the error is that the immediate window call expects a collection, but isn’t receiving one.

Therefore, to properly test your functions returning a collection data type create a test subroutine that calls the function and receives the collection returned. Such as:

``````Sub Test()
Dim a As Collection
Set a = GetCollection()
Debug.Print a.Item(1)
End Sub``````

Then in the immediate window enter the name of the subroutine just created:

``Test``

You should then see the following output:

``````Test
Hello``````

# Conclusion

When testing functions that return a `Collection` data type instead of using the immediate window defer instead to calling them from a test subroutine. Then output the desired response to visually check your code works, or instead of printing to the immediate window, use `Debug.Assert` like this:

``````' Debug.Print a.Item(1)
Debug.Assert a.Item(1) = "Hello"``````

If there’s a problem with your function when the subroutine is ran in the immediate window it will break at the failed assertion test.

Either way, learning this has helped brush up my rusty Excel VBA skills as I jump back into Excel VBA 2016.

Categories

## How to Customise Your Mac Using Dot Files

There are many ways to customise your Mac and now thanks to GitHub other Mac users have shared their valuable dotfiles which show how you can change your Mac to perform a certain task, or look a certain way.

I’m not sure whether my method below is the most correct way for getting this to work, but here’s how I enabled some of my favourite features from Mathias Bynens’ Mac dotfile:

1. Open up Terminal (or your favourite text editor) and navigate to your home directory:
``\$ cd ~``
1. Create a new dotfile:
``\$ touch .osx``
1. Open the newly created dotfile and copy and paste your favourite snippets into this file (you can pick and choose whatever you want)
``\$ nano .osx``
1. Once you’ve finished, save and close the file.

2. Now open up your `.bash_profile` file and append the following code into it:
``````# Load ~/.osx
for file in ~/.{osx}; do
[ -r "\$file" ] && source "\$file"
done
unset file``````
1. Save and close your `.bash_profile` file.

2. Reload your `.bash_profile` file by running:
``\$ source .bash_profile``
1. If that doesn’t make any difference try rebooting your Mac.

Some of my favourite macOS settings from Matthias Bynens’ `.macOS` dotfiles were as follows (be sure to include the comments for each line added so that when you come across them in the future you know what the command was doing):

``````
# When performing a search, search the current folder by default
defaults write com.apple.finder FXDefaultSearchScope -string "SCcf"

# Avoid creating .DS_Store files on network or USB volumes
defaults write com.apple.desktopservices DSDontWriteNetworkStores -bool true
defaults write com.apple.desktopservices DSDontWriteUSBStores -bool true

# Remove Dropboxâ€™s green checkmark icons in Finder
file=/Applications/Dropbox.app/Contents/Resources/emblem-dropbox-uptodate.icns
[ -e "\${file}" ] && mv -f "\${file}" "\${file}.bak"

# Automatically hide and show the Dock
defaults write com.apple.dock autohide -bool true
``````

Once you have created your dotfile it would then be prudent to upload it to the cloud, such as GitHub, then when your computer goes on the fritz, and you have to start again, once you’ve reinstalled your core operating system you then pull down your customised dotfile and perform the following above to get it back to your settings.

This is an especially fantastic tool if you are regularly into the habit of nuking your computer once a year.

Categories

## How To Manage WordPress CSS Using Brackets & Dropbox

Unfortunately Dropbox changed their policy on accessing documents publicly, so this process no longer works. Left for posterity.

A more effective way of managing the stylesheets within WordPress is by using the new Brackets editor and Dropbox.

If you want to be able to manage your CSS styles with `git` and yet also want to automatically manage the minification of your CSS files as well as the transfer of your stylesheets to your WordPress installations, then read on as to how we manage this.

# STEP ONE

First, if you haven’t already got a free copy of Brackets, go and get it. I’ll wait.

# STEP TWO

After installing Brackets, click on File > Extension Manager and then select the Available tab. In the search box enter the word Minify and then click the Install button next to the Minifier extension.

# STEP THREE

Copy your existing style.css file from your theme’s WordPress editor and copy its contents into a new Brackets file. When we return to this file we will eliminate the majority of its content, bar the theme’s comment box.

# STEP FOUR

Save the new CSS file in Brackets to a location on your computer. I use a location within my Sites folder on my Mac OSX (eg. `~/Sites/location/CSS/`)

You should also upload any CSS dependant images into this same directory. My WordPress theme has a couple of little icons which are referenced in its CSS, so I need to create and upload those files into the same area where I have stored my CSS file (eg. `~/Sites/location/CSS/images/`).

# STEP FIVE

Once you have copied your CSS file, create a minified version of it. In Brackets simply click on Edit > Minify. For ease of automation you can also select Edit > Minify on Save which will save any edits to the initial minified file created.

(I will note that the minified file will need to be created first using Edit > Minify before you can use the Minify on Save command â€“ it doesn’t seem to work if you select it first and then save your CSS file.)

# STEP SIX

Ensure you have Dropbox installed and opened and that you have the Public folder synced onto your computer (you can always check by looking into your Dropbox’s settings and on the Advanced tab clicking on the “Selective Sync” button and seeing what is selectively synced from Dropbox to your computer).

We will now symlink the CSS files into this folder. Open up terminal and run the following command:

``\$ ln -s ~/Sites/location/CSS/* ~/Dropbox/Public/MySite/CSS/``

Where `~/Sites/location/CSS/*` is the location of your existing stylesheets and images, and `~/Dropbox/Public/MySite/CSS/` is the location of where you would like to have the stylesheets and images publicly accessed. Please note that the last folder in the Dropbox Public folder should not be created â€“ the symlink command should create it for you.

# STEP SEVEN

Get the public link from within Dropbox and wrap it in a style sheet link tag.

``<link rel="stylesheet" type="text/css" href="https://dl.dropboxusercontent.com/u/00000000/css/main.min.css">``

Where `https://dl.dropboxusercontent.com/u/00000000/css/main.min.css` represents your public link from Dropbox.

# STEP NINE

Go back to your theme’s WordPress editor and remove all CSS details that have been inserted into your stylesheet from Dropbox. This should just leave you with the theme’s comments identifying the name, author and other details of the stylesheet.

Voila!

Now you can edit your stylesheet directly from your computer and any edits saved are automatically sent to your live WordPress sites! Be careful with this â€“ you may want to implement a versioning control system such as git just to protect anything untoward that may happen.

Categories

## How To Manage Multiple WordPress Sites As A Developer

There’s a few things I’ve had to learn with managing multiple WordPress sites for my clients and sadly I’ve had to learn them the hard way. I suppose if I had known some of these techniques earlier on in my WordPress front-end development it would have saved a few sleepless nights as well as some heart-ache.

Here are a few things that I would teach myself if I went back into a time machine when I just first started out:

The first MAJOR issue I encountered when I started developing and using WordPress was that I assumed that all things would be perfect and that nothing would ever go wrong with the servers or database my WordPress sites. Boy was I in for a rude shock! If there’s one thing you need to understand VERY EARLY on when developing your WordPress site â€“ it’s that THINGS DO BREAK.

Look for solutions to house not only your written content, but also your stylesheets, your JavaScript code and your photos. There are two services that I recommend, and I only recommend these as they backup each WordPress site to my personal Dropbox account.

1. WordPress Backup 2 Dropbox â€“ this is a good free alternative and allowed you to back up not only specific areas within your site, but also your database! A good starting solution for your backup needs.
2. ManageWP â€“ primarily a paid alternative this service does pretty much everything you need in being able to manage your WordPress sites all from one central user-friendly location. You can pretty much do anything you want in this area, including: backing your sites up (both content and database), clone it, analyse SEO performance and much more. I highly recommend this solution as a great way of managing ALL aspects of your WordPress needs and the fees are very manageable.

## How are you managing your stylesheets and JavaScript?

If I had a dollar for every time I accidentally pasted the wrong CSS code into the wrong WordPress installation or overwrote my JavaScript with CSS code I would be a multi-millionaire!

If there’s one thing I have learnt the hard way in managing multiple WordPress site’s it is this:

NEVER EVER EVER EVER (AS IN EVER!) EDIT YOUR STYLESHEET OR PHP FUNCTIONS OR JAVASCRIPT CODE IN WORDPRESS

Edit your code in one of your favourite text editors (I like Coda and Sublime Text some others also like Brackets) and where possible structure your folders and files appropriately to help you easily identify the WordPress site you are working on.

For example, don’t create a folder called “SITE ABC” and have within that folder the files “stylesheet.css”, “header.js”, “footer.js” and “functions.php” and then have another folder called “SITE XYZ” which has the same name for its files inside too! Have each file CLEARLY marked within your folder structure what the name of the WordPress install is, for example: “abc-style.css”, “abc-header.js”, “abc-footer.js” and “abc-functions.php”.

Even if you don’t follow the naming convention for your files at least by storing each important file within a folder structure will help you to manage these aspects more proficiently.

(I also utilise Git in these folders to help manage the reason for changes as well as creating a log of events for clients who want to know what has been done. Maybe this will be for another post!)

## How are you optimising your WordPress sites?

There are several popular WordPress plugins out in the wild to help you manage the speed and delivery of your site to your users. Two of which I have either used or are still using are:

1. WP Super Cache â€“ a free and easy way of creating cache files of your posts and pages.
2. W3 Total Cache â€“ a primarily free yet also paid plugin that can help manage your site’s cached posts and pages. I use this plugin predominantly for my sites.

Anyway, three questions I would highly recommend you consider when you start a WordPress site!

Categories

## How To Insert Equation When Equation Editor Greyed Out

If you open up certain types of Microsoft Word documents you will find you’re unable to insert an equation as the icon used to insert equations into your document cannot be clicked and looks greyed out.

Something that looks a little like this:

Thankfully the solution is quite simple.

To get around this problem simply save the Word document as a .docx file as the current working document will have [Compatibility Mode] enabled (as seen in the screenshot above).

Once you’ve saved the document into a compatible file you’ll have no problems clicking on the Equation Editor icon and embedding your juicy equations in.

Categories

## Solved! Explain Everything Powerpoint Issues

I’ve noticed with the Explain Everything app that Powerpoint doesn’t seem to work too well with it.

Upon importing a PPTX file from Dropbox into the Explain Everything app the font and settings of the Powerpoint slides weren’t looking good. Here’s a sample of how the imported Powerpoint file can look:

And here is how the same Powerpoint slide looks in Explain Everything app:

Can you spot the differences between the two slides?

Another problem is also the rendering of Math equations, here is a shot of the original equation in Powerpoint:

Here is the corresponding math equation slide in Explain Everything app:

# Solution

If you would like to import your Powerpoint slides into the famous Explain Everything iPad app for screen-casting you should look at these alternative ways if you’ve found, as I have, that there isn’t a seamless transition from your Powerpoint presentation to the way it’s displayed within Explain Everything.

These ideas only work if the slides are static – i.e. there are no animations or movies/videos in them

The two solutions I have used are:

## 1. Export your PPTX file as PDF

This can be done from any Office Mac application and is a simple matter of printing the slides with the option of selecting to PDF.

However, if your backgrounds aren’t white you will notice a rim around your slides as seen here:

A couple of solutions around this issue are:

1. Make your slides only have a white background, therefore, it will look as though the whole page is a slide.

2. Export your PPTX to JPEG’s

Within Powerpoint you have the option of exporting your slides as JPEG’s. Simply click File > Save As Pictures…. Before you select the destination click the “Options” button in the lower left part of the window and make sure the dimensions of the slides are set to width 1024 and height 768, as show here:

The benefits of this approach is that you will have full bleed slides to edit and write over and will not suffer from the same white stripe problems as PDF exporters will.

Categories

## 2012 Reflection & Working Environment

At the end of every year I like to look back and see what was changed and why I changed it. This helps me to realise where I’m at and where I’m going. It also helps steer my time. I’ll explain more about this as I progress through this post, but I highly recommend looking back at the changes you’ve made throughout the year and why they were made to see where you’re progressing in the new year.

The biggest changes that I made throughout this year were:

## IaaS Services

This year I moved away from Amazon’s AWS (IaaS) and moving to Redhat’s OpenShift (PaaS).

This has helped me immensely in being able to focus on what I enjoy most: writing content, styling sites, toying around with HTML and Javascript (i.e. – all the front-end stuff).

I certainly valued the time spent learning server-side technologies, but found at the end of the day that I was breaking things too frequently and when my sites crashed I’d be scratching my head for a few hours trying to get things back up.

Now I just focus on the front-end, and the OpenShift guys make everything else work. I don’t have to worry about setting the Apache web server, PHP mail, and all that jazz. Another wonderful benefit is that the cost of setting up a site is (currently!) free – with more speed and processing power additional gears can be purchased. Definitely check OpenShift out! I’ll to report again soon on the stuff I enjoy about OpenShift.

## WordPress Themes

This year I moved away from WooThemes to Genesis.

While I certainly haven’t given up on everything WooThemes, as I still use WooCommerce, I have found applying CSS changes easier in the one file Genesis provides.

I also found Woo Dojo’s custom CSS a little annoying when applying CSS styles that required apostrophes `font-family:'Rouge Script',cursive;`. WooThemes would escape these characters and therefore be unapplied in the browser (I even tried using HTML entity replacements, but it still didn’t work). Genesis didn’t seem to have any problems when adding CSS styles in the `</head>` textarea.

## Moving into 2013

### Coda 2 IDE

I’ve enjoyed the ease and use of Coda 2 throughout the year, and I’m still continuing with it. I found with moving to a new MacBook Air at the end of this year, from a MacBook Pro, to be quite seamless with this piece of software thanks to it using iCloud (yes, I bought it from the App Store).

### WebStorm IDE

This piece of software has been great with handling JavaScript. I purchased a licence throughout the year and have been tinkering around with it for small projects. Certainly don’t use it as much as Coda, but would need to watch a few videos to become more acquainted with it.

### WordPress

Am still enjoying the ease of creating websites for clients with this platform, especially with great plugins like WooCommerce.

### MeteorJS

I have tinkered around with this throughout the year, but not enough to really create anything meaningful. This has therefore hindered my development in it and every time I have a spare moment I’ll pick it back up and try to run something with it, but inevitably drop the ball.

I will continue to monitor its progress and hopefully I can design something useful to see whether it suits my needs. Am also hoping for more screencasts on its use, but I think it would need to mature further before training sites begin creating courses on how to use it. Maybe 2013 will be the year for Meteor.

## Leaving in 2012

### Sublime Text 2

It seemed everyone went crazy over this IDE and like the rest of the coding community I tried it out and tinkered around with it. It has a wonderful community that provides heaps of additional plugins for extending the software’s use, however, I didn’t find it as user-friendly and meeting my needs as much as the other two editors (Coda & WebStorm) mentioned above. Personally I think one needs to be careful in software being created and maintained by an individual – I think it runs the risk more of being abandonware, especially if it’s not open-sourced. There’s only so much one person can do, but a group…!

## Currently playing around with

### RStudio IDE

If there’s one thing I really love about this free little application with web development it’s the creation of content. Most web developers use Markdown and there are an array of wonderful apps that can render Markdown pages into HTML pages well, however, RStudio has taken it up an extra notch. In integrating this software with Pandoc you can edit your `.Rprofile` settings to make images `--self-contained` by using the `data:base64` attribute in `<img>` tags. It’s a great little feature for applying to small inline images and I’ve been amazed at the results.

## Watching in 2013

### Brackets

It certainly has piqued my interest with its screencast on core functionality. I haven’t used it yet, but will certainly be keeping an eye on it.

### Laravel 4

I haven’t had much of an opportunity to play around with it, but will be keenly keeping an eye and possibly tinkering around with once it’s released. Hopefully it can be released soon (before I go back to work).

Anyway, that’s been the year for me and what I’m working with currently.