# Combining and Formatting Times

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Combining and Formatting Times.

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 (12040) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Combining and Formatting Times.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

 *Name: Email: Notify me about new comments ONLY FOR THIS TIP Notify me about new comments ANYWHERE ON THIS SITE Hide my email address *Text: *What is 5+3 (To prevent automated submissions and spam.)

Michael Avidan - MVP    18 Aug 2012, 17:22
In many occasions, like this one, the IF statement can be eliminated and the following formula will return the same result.

=A1+(B1="pm")/2

Michael Avidan
“Microsoft®” MVP – Excel
ISRAEL

# Our Company

Sharon Parq Associates, Inc.

# Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Excel Products

Word Products

# Our Authors

Author Index

Write for Tips.Net