Excel: Formatting

Description: There are many format options available which we are not aware. As there are lots of format options, this section is not going to explain all options. It will give an example where the usage of a bad format resulted in wrong business data.

Scenario: We have the start date and end date of a project. We need to calculate the project duration in hours. The formula used is simple and placed the cell format as hh:mm. The duration between 15th April 8 AM and 15th April 5PM is calculated correctly [9 hrs]. But the duration between 15th April 8 AM and 16th April 5PM is also calculated as 9 hours, which is wrong. Heredue to the wrong formatting, formula is avoiding the date, it considering only the time.



Now let us see the correct format option. Noticed the formula used is same. Only difference is the cell format where it changed from hh:mm to [hh]:mm.