Alun Rowe

ramblings about cars, tech, web development and more. BOTB Winner in 2010

Category: excel

Adding days to a date in Excel

It’s one of those things I end up googling overtime I need to do it so, in the spirit of this blog, I’m going to write down everything I learn about Dates and MS Excel here so I know where to find it in the future!

How to add a day to a date in Excel

Assuming your existing date is in Column A Row 1 all we need to do is

=DATE(YEAR(A1), MONTH(A1) , DAY(A1) +1 )

So it therefore follows if you want to add a year to an excel date use:

=DATE(YEAR(A1) +1, MONTH(A1) , DAY(A1) )

And if you want to add a month to an excel date use:

=DATE(YEAR(A1), MONTH(A1) +1, DAY(A1) )

If you want to get clever you can insert a value from another field like this:

=DATE(YEAR(A1), MONTH(A1) , DAY(A1) + A13 )

or if you want to get it from a static cell either use the $ identifier to lock the row or the column e.g.: $A1 would lock the column and A$1 would lock the row OR use both $A$1 to lock both the column and the row. You can also use NAMED CELLS.

Have fun and I’ll add new excel date tips to this as I find things out. Feel free to post info you find in the comments

Excel/VBA equivalent of the PHP Implode function

On a recent data import between Xero and OnePageCRM I needed to turn multiple column address into an address list into a single line.  For this I needed the PHP implode function so I wrote one for Excel.

The data was structured like this:

And I needed this:

My immediate faction was to use the implode function that I use in PHP everyday but it appears to be missing in Excel so I quickly wrote one.

To add this code you need to do the following:

  1. Open your work book
  2. Press ALT + F11 and the code window should appear
  3. Right click on VBA project and choose INSERT then MODULE
  4. Copy paste the following code:

 

Function IMPLODE(Rng As Range, Sep As String)
Dim TEMP As String
For Each Cell In Rng
If Cell.Value = "" Then
Else
TEMP = TEMP & Cell.Value & Sep
End If
Next Cell
TEMP = Left(TEMP, Len(TEMP) - Len(Sep))
IMPLODE = TEMP
End Function

Now in your worksheet go to the cell you want the combined field to be added to and type

=IMPLODE(A1:A3, ",");

and Excel will magically merge the 3 columns A1, A2, A3 together and separate them using the separator you defined.

Enjoy!

Excel CSV import returns an SYLK file format error

The Problem:

Trying to open CSV file that my app had generated kept causing the following message to appear in Excel:

Excel has detected that ‘balhblah.csv’ is an SYLK file, but cannot load it. Either the file has error or it is not a SYLK file format. Click OK to try to open the file in a different format’ , you may also get the message SYLK: File format is not valid

The solution:

After many hours of pulling my hair out trying to work it out I discovered that if you make the first letters “ID” of a text file Excel incorrectly assumes you are trying to open an SYLK file (whatever one of those is!).

Hope that helps!

PS Microsoft have a page on this now… http://support2.microsoft.com/kb/323626

Powered by WordPress & Theme by Anders Norén