英文:
Converting Excel values from 24h to 12h
问题
我有一系列需要转换的时间，目前它们在两列（J和K）中，我需要它们放在一个单独的列中，以12小时制显示。
例如：
| 列 J | 列 K |
|---|---|
| 1403 | 1430 |
| 855 | 1000 |
我需要它们的格式如下：
| 列 J | 列 K | 列 L |
|---|---|---|
| 1403 | 1430 | 02:03 PM - 02:30 PM |
| 855 | 1000 | 08:55 AM - 10:00 AM |
我尝试了十几种不同的方法，但我最多只能将这些列转换为各种12:00 AM的变体。
英文:
I have a series of times I need to convert, currently they're in two columns (J & K) and I need them to be in a single column, as 12 hour times
eg:
| Column J | Column K |
|---|---|
| 1403 | 1430 |
| 855 | 1000 |
And I need them in the format of
| Column J | Column K | Column L |
|---|---|---|
| 1403 | 1430 | 02:03 PM - 02:30 PM |
| 855 | 1000 | 08:55 AM - 10:00 AM |
I've tried a dozen different methods and the best I can do is convert the columns just to variations of 12am
答案1
得分: 0
在单元格L2中使用以下公式，并根据需要复制：
=TEXT(TIME(LEFT(J2,LEN(J2)-2),RIGHT(J2,2),0),"hh:mm AM/PM") & " - " & TEXT(TIME(LEFT(K2,LEN(K2)-2),RIGHT(K2,2),0),"hh:mm AM/PM")
请注意：尽管这看起来很漂亮，但实际上使时间更难处理，因为现在它以文本形式存储。每当需要处理时间时，您将需要从文本中提取时间。我建议将时间存储为数字，使用三列，并对时间列应用自定义格式。以下是一个示例：
英文:
Use the following formula in cell L2 and copy down as required:
=TEXT(TIME(LEFT(J2,LEN(J2)-2),RIGHT(J2,2),0),"hh:mm AM/PM") & " - " &TEXT(TIME(LEFT(K2,LEN(K2)-2),RIGHT(K2,2),0),"hh:mm AM/PM")
NOTE: While this may look pretty it actually makes the time harder to work with since its now stored as text. You will need to strip the time from the text any time you need to work with it. I would recommend storing the time as a number, using three columns, and applying a custom format to the time column. Below is an example:
答案2
得分: 0
An alternative approach using TEXT( ) Function.
• Formula used in cell L2
=TEXT(
TEXT(J2, "00:00"),
"hh:mm AM/PM"
) & " - " &
TEXT(
TEXT(K2, "00:00"),
"hh:mm AM/PM"
)
Or, Using TEXTJOIN( )
• Formula used in cell L2
=TEXTJOIN(
" - ",
,
TEXT(
TEXT(J2:K2, "00:00"),
"hh:mm AM/PM"
)
)
英文:
An alternative approach using <kbd>TEXT( )</kbd> Function.
• Formula used in cell <kbd>L2</kbd>
=TEXT(
TEXT(J2, "00\:00"),
"hh:mm AM/PM"
) & " - " &
TEXT(
TEXT(K2, "00\:00"),
"hh:mm AM/PM"
)
Or, Using <kbd>TEXTJOIN( )</kbd>
• Formula used in cell <kbd>L2</kbd>
=TEXTJOIN(
" - ",
,
TEXT(
TEXT(J2:K2, "00\:00"),
"hh:mm AM/PM"
)
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论