Sep 20, 2023
format military time from xxxx to xx:xx
Details
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Oct 7, 2023
these are the cells staff enters their times. the military boys are used to entering
4-digit numbers: 0630 and i need to format them as shown
Hi Ian,
To automatically format those cells as
06:30
, choose Format > Number > Custom number format and enter 00:00
in the box.still have it be usable in calculations
Now when you enter a number like
630
in a cell, it gets formatted automatically and looks like 06:30
, but the value in the cell will remain 630
. To use it as a time value in calculations, you can convert those formatted numbers into time serial values that look the same with a formula in another column.The reason why this must happen in another column is that you cannot change a value "in place" with a spreadsheet formula. What you can do is insert the conversion formula in another column and convert the value so that it shows in that other column. You can then refer to the new column in your formulas elsewhere.
To convert the formatted numbers in column
C2:C20
into proper time serial values, use this formula in cell H2
:=arrayformula(query(
value(left(C2:C20, 2) & ":" & right(C2:C20, 2)),
"format Col1 'hh:mm' ",
0
))
The results will be displayed in cells
H2:H20
automatically. When there is no number in a particular row in column C2:C20
, column H2:H20
will be blank in that row.You can copy the formula cell to
I2
to convert the column D2:D20
the same way.Finally, to calculate the duration between the start times in column
C2:C20
and the end times in column D2:D20
, put this array formula in cell F2
:=arrayformula(if(isnumber(I2:I20), I2:I20 - H2:H20 + (H2:H20 > I2:I20), iferror(ø)))
See 'Solution2' in the sample spreadsheet.
To learn more, see how date and time values work in spreadsheets for a detailed explanation.
Cheers --Hyde
Last edited Dec 2, 2023
Diamond Product Expert --Hyde recommended this
Helpful?Upvote Downvote
All Replies (2)
Sep 20, 2023
Hi Ian,
Try something like this:
=query(value(left(A2, 2) & ":" & right(A2, 2)), "format Col1 'hh:mm' ", 0)
If you need more help, please share a sample spreadsheet that shows realistic-looking sample data and manually enter the desired results you would like to see there, in the very cells where you would like see them. Clearly indicate where the sample data is, and where the desired results are.
Cheers --Hyde
Oct 10, 2023
Oct 10, 2023
in Excel, you can create a custom format
Hi Ian,
But of course you can use a custom number format like
00:00
— as long as you are entering numbers. The formulas I gave you will work as is. See Solution2.What you cannot do is convert a text string to a duration through a custom number format, which is what your sample spreadsheet appeared to demonstrate.
Cheers --Hyde
Nov 3, 2023
Let me add to contribution from Product Expert Hyde
Custom number format
00:00
will work whether your entry is
0630
or
630
Cheers!
Yogi Anand
Nov 10, 2023
Ethan Woodard wrote:
HI Everybody how do you erase your history
It is considered bad form to try and hijack someone else's thread to ask an unrelated question.
This forum is for Google Sheets, and I am not sure if your question is on-topic here. If the question is in fact about Google Sheets, please post it in a new topic. If it is not, please find a relevant forum.
I would recommend that you delete your post from this thread.
Cheers --Hyde