Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Combining and Formatting Times.
Written by Allen Wyatt (last updated October 2, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Brenda has an Excel worksheet with two columns: A and B. In column A are times without any indication of whether they are AM or PM. Instead, column B contains either AM or PM, as appropriate for the cell just to the indicator's left. Brenda would like to combine these two columns into one column and have the result formatted as military time. Thus, if A14 contains 05:11 and B14 contains PM, she would like the combined column to contain 17:11.
There are very easy ways you can get the desired results in a column, but the formula you choose depends on the way the times are stored in column A. If the times are actually time values, then the following formula in column C will work just fine:
=IF(B1="pm",A1+0.5,A1)
The formula works because it adds half a day (0.5, which is an Excel time value for twelve hours) to the time in column A if the indicator in column B is "pm". It will work whether the indicators in column B are lowercase ("pm") or uppercase ("PM"). It won't work, however, if you have any times in column A that are past noon, such as 12:15. In that case, you'll need to modify the formula a bit:
=IF(B1="pm",IF(A1<0.5,A1+0.5,A1),A1)
If the time stored in column A are actually stored as text, then you'll need to do a conversion, but it is extremely easy to do:
=VALUE(A1 & " " & B1)
Regardless of which formulaic approach you use, you'll need to format the cells containing the formulas so that they show times in military (24 hour) format. You can pick one of the pre-defined time formats (shown in the Number tab of the Format Cells dialog box) such as 13:30 or 13:30:55, depending on whether you want seconds displayed or not. You could also define a custom format such as HH:MM, H:MM, HH:MM:SS, or H:MM:SS.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12041) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Combining and Formatting Times.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Work with times in a worksheet and you will eventually want to start working with elapsed times. Here's an explanation of ...
Discover MoreNeed to check the current time in a formula you are putting together? It can sometimes be tricky to remember what Excel ...
Discover MoreWant to display an elapsed time as a decimal format? It's pretty easy to do, if you know that a very simple formula is ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-10-02 23:35:20
henry
Sometimes it is just faster to sort the column, then manual copy paste simple formula.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments