Sep 20, 2023

format military time from xxxx to xx:xx

the time data taken in is in standard 4-digit military time (eg. 0630).  i need to convert the display, retaing leading zero and insert the colon (eg. 06:30) as well as still have it be usable in calculations.  have no idea where to do this.  some help is appreciated.
Locked
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Recommended Answer
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?
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
sorry about the confusion.  and thanks for all your help.  very much appreciated.  in Excel, you can create a custom format.  if anything non-numerical is entered or is out of range, it errors out.  but that's the difference in a suite of installed ware vs. a web application.  :)  no matter, i've learned a lot from this and your help has been invaluable.  thanks again.  :D
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
Hi Ian:

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
HI Everybody how do you erase your history 
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

false
13812439617163476472
true
Search Help Center
true
true
true
true
true
35
Search
Clear search
Close search
Main menu
false