Excel Custom Number Formats A Second Time

Last week I published an article about Excel auto sums and custom number formats, but I feel the number formats need a more detailed explanation than I gave in that tutorial. Therefore I have now recorded another Excel demo only about custom number formats.

Sort converts number into text
A common mistake when you are not very used to Excel, is to add a sort after a number. In most cases that will make any formulas useless, as Excel will consider the number with the sort as text and not a numerical value. You can see when this happens, as the content of the cell will become left-aligned.

Use number formats instead
It is of course useful to have a sort in the numerical cell, to make sure you are not comparing apples and pears, but to be able to use a sort for your numbers, you must use a number format. Then Excel will understand that this is not text but still numbers, and you can do all calculations just as if there was no sort at all.

If you want to use a currency, Excel gives that possibility in a dropdown, but for other sorts you need to create a custom number format.

Hash and zero
When you choose to create a custom number format, Excel shows a lot of examples on different ways to write a number. You can use any of the formats with a hash mark and a zero. Select it, and an example result is shown above in the same dialog.

What I add in the demo and what is NOT given by Excel is the sort. After the number format you wish to use for the numericals, add the sort within quotation marks. If you like a space before the sort, like I do, enter that also. Then the sort addition to the number format will be like
" tons". That's it!

Now Excel will treat all cells that have this format and has a number followed by tons as a number and not as text, and you can use all the Excel calculation options on those cells and still see what sort you are counting with.



More tips on kalmstrom.com
In the Tips section of the kalmstrom.com website, you can find more info about the Custom Number Formats Tip and many other tips on SharePoint, Excel and Outlook. There are also a few articles on other subjects. You are very welcome to browse around! I hope you will find something useful.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

.

TimeCard Single for Outlook V5 Released

Today I am happy to announce the official release of TimeCard Single V5. I am grateful to the Beta testers who gave us valuable input – and to the kalmstrom.com team members for their skilled and dedicated work.

New architecture
TimeCard is new under the surface too. The code is modernized, and in some parts TimeCard has been totally rebuilt.  This new architecture will make future development of TimeCard easier.

Example data
For the first time we have added example data to TimeCard. These are generated randomly, so sometimes combinations of customers, tasks and subjects in the example appointments might be a bit unrealistic, but they will still be a good help for TimeCard evaluators.

New design
TImeCard V5 has a new design, with four different screens and a main settings screen where we earlier cramped everything into one screen. It looks much better and gives a clearer distribution of the settings features.

TimeCard may be customized in many ways, but if you use the default settings you only have to use two of the screens: the Costs and Rates screen and. the Tags screen. Here you will find the most important TimeCard settings:  how your costs should be calculated and what data you should tag your appointments with to convert them into time sheets.

Documentation update
I have recorded ten new video demonstrations for TimeCard Single, and the manual and slideshow have been updated, as well as the website. We hope both new and old users will find TimeCard V5 easy to understand and use.

More versions
While the kalmstrom.com QA team has been testing the new version of TimeCard Single, the developer group has begun updating the organizational version of TimeCard. Much of the Single version rebuild can be used in TimeCard Workgroup too, so even if it is more complicated we hope we will not have to wait too long for the release of that version too.



Upgrade or try
Users who have a valid support subscription are welcome to request a new key for TimeCard V5 and upgrade. TimeCard will know that you have an earlier version and upgrade it when you run the installer.

Have you not tried TimeCard yet? If you need a tool that helps you keep track of how you spend your time, I suggest that you download the solution and evaluate it for 30 days. Please study the video demonstrations and the manual, and remember that you are always welcome to contact us if you have questions.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

Moving Millions Of Files Into SharePoint - An Interesting Challenge

I have recently been hired to lead a project where millions of files will be moved into SharePoint. It is not an easy task, because there are many things to consider, but it is an interesting challenge that I am happy to take on.

Consultation assignments
The first kalmstrom.com solutions have been with me for nearly fifteen years, and the kalmstrom.com brand is the business creation I am most proud over. But I have a team of skilled co-workers assisting me with the kalmstrom.com development and documentation, so I have time to accept assingments outside my own company.

These assignments usually gives me interesting input from new people and challenges that make me grow as a developer. I can often make use of the knowledge and inspiration I get from these assignment when I design new versions of my own products.

SharePoint 2013
I am a Microsoft certified SharePoint expert, and it was my SharePoint knowledge and experience that qualified me for this assignment. I have now set up a prototype of a SharePoint 2013 farm that will be used by the IT department as a first prototype.

2 million files in 225 000 folders 
The organization needs to keep a huge amount of documents on file in a way that is both secure and easy to search. It has 225 000 directories containing around 2 million files, so my first task was to do a file server inventory.

The inventory has given an opportunity to clean-up and determine what should be kept for the future. It is now completed, and we can go over to the difficult decisions on what to do with all the files.

PowerShell automates tedious work
Before moving the 225 000 folders to SharePoint, we must consider what should become of them. Should the content of the folder be transformed into a site collections, a site or a document library, or should it be used as metadata or even tags? Is the information stored in Excel lists suitable to export into SharePoint lists?

To migrate the data manually would take years, but once the decisions are made the actual migration is done with PowerShell. All the SharePoint containers are automatically created and populated with the correct files.

Metadata should be kept
An important aspect to consider is the existing metadata, that should not be lost in the transfer to SharePoint. Such metadata could be the information already tagged by the file location. A file that exists under P:\Projects\2010\IT\ClientPlatform\ already has a lot of metadata that should be maintained. It should be tagged as a project file, created in 2010, by the IT-department, concerning the ClientPlatform project.

Office update
In the process I am engaged in, the organization also converts all of their Office files into the new file formats. That means that the old .doc and .xls files will be converted into .docx and .xlsx formats, which gives a huge space saving benefit and enables the new Office features.

Search options
Another consideration when I design the new SharePoint system is how information should be found in the future. It should still be possible to find information by location, but we should also use the SharePoint search features. Here I can do a lot by using the SharePoint features in combination with the maintained metadata.

Long term project
This SharePoint project will probably continue a for at least six months more. It would be difficult for me to work full time with a big project for such a long time, but the managers are very flexible and I can devote myself to this project when I am free for other commitments. This is a situation that suits me very well, and I am looking forward to seeing the project develop. .
By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

Auto Sums and Custom Number Formats

In my latest Excel tutorial in the kalmstrom.com Tips section, I am showing four ways to make Excel calculate sums and one way to add a custom number format. I am using Excel 2013 in the demo, but what I explain is applicable to earlier versions of Excel also.

Series of Excel demos
In the kalmstrom.com Tips section you can find demos about SharePoint, Outlook and Excel, and I have also given a few tips on other platforms. Some of the Excel tips are about how Excel tables and charts can be incorporated in SharePoint, but there are also a series of tips on Excel calculations and formulas. The demo below belongs to that group.

AutoSum button
When you want to make Excel calculate for you, you can use formulas like =A1+A2+A3 or =SUM(A1:A3), but you can also make it easy and use the AutoSum button. However, you should be aware that if no cells are selected but the one where the sum should be placed, Excel will make a guess. This guess is qualified but not always correct, so you have to check it.

To be on the safe side, use the AutoSum button when you have selected the cells to be summarized. Then Excel will calculate the sum of the selected cell in a correct way.

Custom number format
Excel gives many options for displaying numbers, for example as percentages, with a currency or as a date. You may also create your own number format. Excel gives a lot of examples to select from and maybe change, but you can also write it in like I do.

Custom number formats include a combination of hash marks and zeros used as placeholders. In my example the zero means that the unit should always be displayed, while the hash marks mean that the tens and hundreds should only be shown if they have a value. For example "002" will be shown as 2 in the cell, and "222" will be shown as 222.



Excel reports
The reason why I have been recording so many Excel tutorials this year is that most kalmstrom.com solutions have Excel statistics reports. Each of the products has its own default reports that give a lot of information on how the product has been used. It is also possible to create custom reports on the same data.

All the standard Excel feautres can be used with these statistics reports, and I hope users will make the most of them. Excel gives excellent possibilities when it comes to analyzing data and to show results in graphs and charts.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

.

Details Pane In New Kanban Task Manager For SharePoint

A month ago we released version 2 of Kanban Task Manager for SharePoint, the kalmstrom.com solution for task and project management in SharePoint. It had several good new features but also one was not as useful as we had hoped. Therefore we have now released an update that gives this feature in a better way.

Kanban board in SharePoint
When the traditional kanban board moves into SharePoint we can add things that the physical board does not manage. We can for example show tasks for multiple projects in one kanban board and filter tasks by project, responsible and/or priority.

Another advantage is the possibility to see more information about a task than what is visible on the little kanban card. This feature was added in version 2 of Kanban Task Manager for SharePoint, but unfortunately it had a bad influence over other functions in our solution.

Instead of hovering the mouse over the visualized task to see more task info, users can now right click the task to open a details pane at the bottom of the screen. This is similar Windows 8, where you can right click an app on the start page and get various options for it in a pane that becomes visible below.

Drag and drop works well
The rest of the new features in version 2 work as intended. The most important one was the possibility to drag and drop tasks not only between phases but also within phases.

To make the tasks keep their order when they were dragged both within and between phases was the most difficult part of the version 2 development, and maybe it took too much of our energy so that we did not consider all aspects of the hovering feature.

Better with details pane
The new way of showing more info is actually better than the hovering, as you can keep the details pane without having to hold the mouse cursor in a certain position.
When you have already opened the details pane by right clicking on a task, you can just select another task by either left or right click to see its details in the pane. You can also use the arrow keys to move between tasks. Right click in an empty space to close the details pane.

Upgrade
A bug connected with hidden phases has also been fixed in this update, so if your organization subscribes to Kanban Task Manager for SharePoint and you use hidden phases or want the new details pane, I recommend you to upgrade your installation(s).

Upgrades are always included in the subscription, just like the support. The quick upgrade process is described on the Kanban Task Manager for SharePoint upgrade page, where you can also download the latest version.

Try it!
Have you not tried Kanban Task Manager yet? In that case you are welcome to download Kanban Task Manager for SharePoint and try it for 30 days. The kalmstrom.com support services are for evaluators too, so you are welcome to contact us if you have any questions.


By Peter Kalmström
CEO and Systems Designer
kalmstrom.com Business Solutions

SharePoint Staff List In 2 Ways

A list of staff within a company or organization can be created in many different ways. Maybe you have an Excel list like the one below where all details about each staff member is filled out? It contains a lot of information but looks rather boring.  
For the team site you would want something nicer, and all information is not needed there. A photo of each staff member is a good addition, though, and it can be handled much better in SharePoint than in Excel. I show how to do it in the demo below.


Two ways
Two different methods can be used to import an Excel list into SharePoint. Both starts from an Excel table, so you must first format the Excel list as a table. Then you can either export this table to a SharePoint list or use an Access query to get the Excel list into a SharePoint list.

The easy way
The first method, to export an Excel table to a SharePoint list, is the easy one. It gives you a new SharePoint list that will be useful after some modification. This modification is done in SharePoint only, so if you are used to working with SharePoint lists it should be quick.

The powerful way
The Access method is more complicated, but it gives the best result. In the kalmstrom.com Tips section we have an article on how to import an Excel table to a SharePoint list. It gives you not only the demo but also a step by step description of this second method.

The result will be a pleasant list with photos of all staff. A click on the name opens a new window with more information about that person. In my demo I have used rather big photos, which looks good but entails a lot of scrolling. I would actually recommend you to use smaller pictures if you want to try something similar.



Access, Excel and SharePoint are potent platforms that work well together. We should make use of that, and in the kalmstrom.com Tips section you can find more tutorials about these three platforms and how to combine them. You are welcome to have a look!

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

Subscribe To Software – Or Buy It?

Four years ago kalmstrom.com introduced a new licensing model: we went from a purchase to a subscription system for most of our solutions. Now subscriptions have been more common in the software business, but we still have to explain our decision to some evaluators.

We are convinced that the subscription model is better for all parties involved, so now we are leaving the purchase model completely. KBase for Outlook now has a subscription licensing, and the upcoming version 5 of TimeCard Single will be subscription based as well.

Always support 
All kalmstrom.com subscription levels include support, so users can feel sure they will always have someone to turn to with questions. They can always contact the kalmstrom.com support if problems should occur, and if we get a description of the situation we might point out new possibilities that the user had not considered.

Assistance can of course be given with a separate support contract also, but with a kalmstrom.com subscription there is no discussion. Users don't have to worry about if they have a valid support contract or not, and the kalmstrom.com support team knows that support should be given to all subscribers.

As we also give support to evaluators, the kalmstrom.com technicians can concentrate on solving problems and answering questions instead of wasting time in finding out if callers have valid support contracts or not.

Subscriptions give incentive to improve
Another customer benefit of the subscription model is that it gives the kalmstrom.com team an incentive to maintain a good relationship with subscribers, so that they stay in our Community and keep renewing the subscription. We want long time relations, not a quick sale and then a hunt after the next one.

Worst scenario for one-time payments is a seller who says "Why should I care? I have already  received the money!" I am too concious of my reputation as a software developer to ever think like that, but all potential customers don't know that. A subscription system should inspire confidence.

Always upgrades
When kalmstrom com solutions are updated with new features and support for new Microsoft versions, all users should be able to take advantage of that. Therefore all subscribers have free upgrades.

Or software is built on top of Microsoft’s, so we often have to adapt them to improvements in the Microsoft platforms. As a Microsoft Silver Partner we have access to preview releases, and we are very early aware of any compatibility issues. Then we can start developing our products to support the new version of Outlook, SharePoint or any other Microsoft products that the kalmstrom.com applications make use of.

Better finances
The subscription model makes finances simpler for the subscriber, as it gives a clear projection of software costs. There will be no extra payments for support or upgrades, as this is already included in the subscription fee. Hopefully, as we do our best to keep current customers happy, the subscription will continue year after year and there will be no costs for changing software.

kalmstrom.com also benefits from the subscriptions, because they give us a predictable and constant revenue stream. Compared to the purchase model this reduces potential uncertainty and risk that the company should go off the market,  Instead it allows us to feel more secure when we invest in enhancing our solutions.

Always keep your data
In spite of these obvious benefits some evaluators say they prefer to buy our products instead of subscribing to them. They might be afraid that their data will no longer be accessible if they cancel the subscription, but we can explain that this is not true.

All data used with a kalmstrom.com product is stored in an open database, Access or SQL Server, or on the subscriber's SharePoint site. Thus it is as accessible after cancellation as it was during the subscription period. The only difference is that the data cannot be used with the kalmstrom.com solution after a cancellation.

Loss of control?
Other evaluators might hesitate to subscribe because they fear they will loose control over the software they use, but this actually has nothing to do with the licensing model. The only way to have control over a software is to own the source code for it and know how to work with that code to update and modify the product.

For those cases we offer our source code for purchase or subscription, and some big organizations and companies with highly qualified
IT teams have chosen to make that investment.

I feel confident we are doing the right thing when we now take the final step and offer all kalmstrom.com solutions with subscription licensing. With a subscription model, team and subscribers work together towards the same goal – to keep the kalmstrom.com solutions alive and growing.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

Customize the Outlook 2013 Calendar Weather

I am continuing my series of Outlook basic demonstrations with a tutorial on the weather forecast display in the Outlook 2013 calendar. I like this new feature that gives me a possibility to see the local weather for several places directly in Outlook.

I travel a lot, so I have several locations in my calendar to switch between, and that way I can always see the local weather of the place I am going to. But if you are not a New Yorker the weather bar needs to be customized.

New York and Farenheit default
The weather settings do not follow the local settings of your operating system. Instead the Outlook 2013 calendar always shows the weather in New York by default. And the temperature scale is Fahrenheit, which is the official scale in the United States.

However, most countries use the Celsius scale, and most people want to know their local weather and not the New York weather in the first place.

Change location
The first step in my customization is to add a new location, and I choose the Swedish town Borgholm, where the kalmstrom.com head office is situated. In the kalmstrom.com Tips article about customization of the Outlook weather I describe that part more in detail.

Change temperature scale
The second step is to change the temperature scale into Celsius, and for that I have to go into the Outlook calendar options. At the very bottom of that screen there are two radio buttons for Fahrenheit and Celsius.

Calendar add-ons
I hope my Outlook weather tip will be useful to many, but when I recorded the demo I was especially considering all users of the kalmstrom.com add-ons for the Outlook calendar.

Calendar Browser is a tool for corporate resource management. With Calendar Browser users can book rooms, cars, IT staff or whatever the organization  wants to make bookable, by making an appointment in the calendar of that resource. Some Calendar Browser subscribers have several hundreds of users, and I am sure they all want the local weather in their Outlook calendars.

TimeCard is a calendar add-on for time reporting. The idea behind TimeCard is that you should report your time in the same tool as you use for planning – the calendar. There is a single version for one person only and an organizational version with central reporting and configuration.

I hope you will enjoy my demo and your own Outlook weather bar customization!


By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions