Get Sum Hours: Minutes (MS Excel) Status Bar Formula Cells to Text Format

GetSumHours:Minutes (MS Excel) Status Bar Formula Cells Text Format

Microsoft Excel is a powerful spreadsheet application that offers a wide range of features to help users manage and analyze data. One such feature is the Status Bar, which can display various information about the selected cells. In this article, we will focus on how to use the Status Bar to calculate the sum of hours and minutes in a range of cells, and how to format the cells' text to achieve the desired result.

Calculating the Sum of Hours and Minutes using the Status Bar

To calculate the sum of hours and minutes in a range of cells, follow these steps:

1. Select the range of cells that contain the hours and minutes data.
2. Right-click on the selected range and choose "Format Cells" from the context menu.
3. In the "Number" tab, select "Custom" from the "Category" list.
4. In the "Type" input box, enter the custom format "[h]:mm" and click "OK".
5. The selected cells will now display the time data in the format "h:mm" (where h represents hours and mm represents minutes).
6. Next, right-click on the Status Bar and select "Sum" from the context menu.
7. The Status Bar will now display the sum of the hours and minutes in the selected range of cells.

Formatting the Cells' Text

To format the cells' text to display the sum of hours and minutes in a more readable format, follow these steps:

1. Select the range of cells that contain the sum of hours and minutes data.
2. Right-click on the selected range and choose "Format Cells" from the context menu.
3. In the "Number" tab, select "Custom" from the "Category" list.
4. In the "Type" input box, enter the custom format "[h] ""hours "" mm ""minutes""" and click "OK".
5. The selected cells will now display the sum of hours and minutes in the format "hours minutes" (where hours represents the sum of hours and minutes represents the sum of minutes).

Code Blocks

Here are some code blocks that demonstrate how to use VBA to calculate the sum of hours and minutes in a range of cells, and how to format the cells' text:

' Declare variables
Dim rng As Range
Dim sumHours As Double
Dim sumMinutes As Double

' Set the range of cells that contain the hours and minutes data
Set rng = Selection

' Calculate the sum of hours and minutes
For Each cell In rng
    sumHours = sumHours + cell.Value / 60
    sumMinutes = sumMinutes + cell.Value Mod 60
Next cell

' Format the sum of hours and minutes
sumHours = Int(sumHours)
sumMinutes = Int(sumMinutes)
If sumMinutes > 60 Then
    sumHours = sumHours + 1
    sumMinutes = sumMinutes - 60
End If

' Display the sum of hours and minutes
MsgBox "Sum of hours: " & sumHours & vbCrLf "Sum of minutes: " & sumMinutes

' Declare variables
Dim rng As Range
Dim cell As Range

' Set the range of cells that contain the sum of hours and minutes
Set rng = Selection

' Format the cells' text
For Each cell In rng
    cell.Value = Format(cell.Value, "[h] """hours" " mm """minutes" """")
Next cell

• To calculate the sum of hours and minutes in a range of cells, select the range, right-click, choose "Format Cells", select "Custom" in the "Category" list, and enter the custom format "[h]:mm" in the "Type" input box.
• To format the cells' text to display the sum of hours and minutes in a more readable format, select the range, right-click, choose "Format Cells", select "Custom" in the "Category" list, and enter the custom format "[h] """hours" " mm """minutes" """ in the "Type" input box.
• To calculate the sum of hours and minutes using VBA, declare variables, set the range of cells, calculate the sum, and format the sum as desired.
• To format the cells' text using VBA, declare variables, set the range of cells, and format the cells' text as desired.

References

• Display or hide column and row headers and gridlines on a worksheet
• Display or hide zero values
• Change the number format of cells
• Use custom number formats to display numbers as you want them
• Range.NumberFormat property
• Format function