In Microsoft Excel there is a lot of knowledge and extremely useful tools for your life. For example, you can see the whole time with just a few small operations. Adding and subtracting time in Microsoft Excel makes it possible to see how much time you’ve spent on your work. Read the article to understand better.
Join the channel Telegram belong to AnonyViet 👉 Link 👈 |
How to add and subtract time in Microsoft Excel
Add time in Microsoft Excel
This will combine formulas and formats to calculate exact time totals in Excel.
Format hour and minute cells
Note that we will be using the H:MM structure to stay in sync with the current hours and minutes. Although Excel will recognize it as you enter data, you can always make sure by formatting your cells.
Select the cells and right click and select “Format Cells” or go to Home, select “Format” in the toolbar and select “Format Cells”.
In the Format Cells window, make sure you’re on the Number tab. Then select “Custom” in Category. Next, select “h:mm” from the list or enter the format in the Type box.
Click “OK” to apply the formatting to the cells.
Add time using the formula
Select the cell where you want to enter your time. Enter the following formula, replacing the cell references with the cells you want.
=F2+F3
Press Enter to apply the formula and you’ll see your total hours and minutes for those cells.
Add time using Sum . function
If you have many cells of data, use the SUM function, which is the easiest choice for you.
Select the cell at the end of the column of data. Go to the Home tab and select “Sum” (formerly named “AutoSum”).
Excel will identify the cells that you want to sum. Otherwise, just drag over the cells containing your time to fill in the formula and press Enter.
You will see the total hours and minutes of the data cells.
24-hour time format
When you add cells that use 24-hour time, you will most likely get incorrect results. To avoid this, you can format the cell containing the result.
Select the cell containing the formula. Right click and select “Format Cells”, or you can choose Home > Format > Format Cells.
In the Format Cells window, select the Number tab, then click “Custom” in the Category and select “[h]:mm;@”.
Click “OK” to apply the formatting to the cell. You will then see accurate results for cells using 24 hours.
Subtract time in Microsoft Excel
You can also subtract the end time from the project start time in Excel.
Format time cells
Just like when adding hours and minutes, make sure your time cells are formatted correctly. In this case, you would format the time to 4:30 am.
Select cells that contain data. Right-click and select “Format Cells”, or you can choose Home > Format > Format Cells.
In the Format Cells window, on the Number tab, select “Time” under Category. Select “1:30 PM” for hour and minute format.
Click “OK” to apply the formatting to your cells.
Except time
Select the cell you want and enter the following formula, replacing the cell references with your own.
=C2-B2
Press Enter to apply the formula.
Next, you may need to format the cell in hours and minutes to show the total time spent. By default, your results may show up according to the time of day.
Select the cell containing the formula, right-click and select “Format Cells” or you can choose Home > Format > Format Cells.
In the Format Cells window, on the Number tab, select “Custom” in the category. Select “h:mm” or enter this format in Type and select “OK”.
You will then see the number of hours and minutes that you have used.
In addition, you can also refer to the free Excel course here.
Source: How to add and subtract time in Microsoft Excel
– TechtipsnReview