How to record time in Excel in military (0730) format?

  • Thread starter Thread starter fontana
  • Start date Start date
F

fontana

I keep a activity diary to appease the reviewer at the long term disability
co. I am used to recording time as military time, but I cannot get Excel to
keep the leading zero unless I format the cell as text. And typing a number
as text elicits an error which is a hassle to correct.
 
In my view it is preferable to include the colon in the format, and thus
make it clearer that it is indeed a time.
 
I do not want to use a colon. When I tried hhmm, I got date and 0000 when I
entered 0945.
 
If you don't enter the colon when you enter your time, Excel doesn't
recognise it as a time.

If you want to enter it without the colon and let Excel store it as a number
945 and then you want to go through the process of translating that number
to a time, you can produce formulae to do that if you want, but that's the
long way round.

The colon is the short way of doing it.
 
Hi Fontana,

Although I fully agree with the remarks everyone has made about hte use of
":" for time, you can try to format the cells as "0000" (without the
quotes).

5 will be displayed as 0005
740 ... 0740
2355 ... 2355

As these are still numbers, calculations on it will not return an error.

Please let me know if this was of any help.

Wkr,

JP
 
Hi David,

Very good remark, tnx. I was too much fixed on the format and not enough on
the time system.

Fontana,

To sum military time, this is the best formula I could think of.

=--(SUM(LEFT(A1:A11;1)*--(LEN(A1:A11)=3))+SUM(LEFT(A1:A11;2)*--(LEN(A1:A11)=4))+INT(SUM(--RIGHT(A1:A11;2))/60)&MOD(SUM(--RIGHT(A1:A11;2));60))

Has to be entered with ctrl-shift-enter.

Although you see always 4 digits, the length of the cell varies from 1 to 4.

(SUM(LEFT(A1:A11;1)*--(LEN(A1:A11)=3)) sums the hours having 1 digit, e.g.
0740
SUM(LEFT(A1:A11;2)*--(LEN(A1:A11)=4)) sums the hours having 2 digits, e.g.
1710
INT(SUM(--RIGHT(A1:A11;2))/60) sums the minutes and convert it to hours
MOD(SUM(--RIGHT(A1:A11;2));60)) returns the sum of the minutes less then 1
hour

Use the & tho link the hours and minutes, the leading -- is to convert this
string back to a value.

Tested on the values below.

0740 07:40
1240 12:40
0530 05:30
0805 08:05
1710 17:10
1820 18:20
0006 00:06
1523 15:23
0933 09:33
0103 01:03
0023 00:23
9553 95:53


Wkr,

JP
 
The simple way of converting the 0040 to a time of 00:40 is to use
=--TEXT(A1,"00\:00") and format the cell as time.
 
Hi David,

Tnx, why and when is the "\" needed in the format string?
The help is not explaining this and it looks to me as an escape character as
frequently used in UNIX commands.

Wkr,

JP
 
No, I don't think help assists much in this case.
Yes, I think you are right in regarding it as equivalent to an escape
character in this situation.
Another example of the same usage is =--TEXT(A2,"00\/00\/0000") to turn
07082009 into 07/08/2009 and thus into 07 Aug 2009 (or 08 Jul 2009,
depending on your Windows regional options, just as if you'd typed in
07/08/2009).
--
David Biddulph


JP Ronse said:
Hi David,

Tnx, why and when is the "\" needed in the format string?
The help is not explaining this and it looks to me as an escape character
as frequently used in UNIX commands.

Wkr,

JP

David Biddulph said:
The simple way of converting the 0040 to a time of 00:40 is to use
=--TEXT(A1,"00\:00") and format the cell as time.
--
David Biddulph

JP Ronse said:
Hi David,

Very good remark, tnx. I was too much fixed on the format and not enough
on the time system.

Fontana,

To sum military time, this is the best formula I could think of.

=--(SUM(LEFT(A1:A11;1)*--(LEN(A1:A11)=3))+SUM(LEFT(A1:A11;2)*--(LEN(A1:A11)=4))+INT(SUM(--RIGHT(A1:A11;2))/60)&MOD(SUM(--RIGHT(A1:A11;2));60))

Has to be entered with ctrl-shift-enter.

Although you see always 4 digits, the length of the cell varies from 1
to 4.

(SUM(LEFT(A1:A11;1)*--(LEN(A1:A11)=3)) sums the hours having 1 digit,
e.g. 0740
SUM(LEFT(A1:A11;2)*--(LEN(A1:A11)=4)) sums the hours having 2 digits,
e.g. 1710
INT(SUM(--RIGHT(A1:A11;2))/60) sums the minutes and convert it to hours
MOD(SUM(--RIGHT(A1:A11;2));60)) returns the sum of the minutes less then
1 hour

Use the & tho link the hours and minutes, the leading -- is to convert
this string back to a value.

Tested on the values below.

0740 07:40
1240 12:40
0530 05:30
0805 08:05
1710 17:10
1820 18:20
0006 00:06
1523 15:23
0933 09:33
0103 01:03
0023 00:23
9553 95:53


Wkr,

JP


"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
Calculations will not return an error, but will give the wrong result
if you wanted them to be treated as times. 0040 + 0040 would give 0080
instead of 0120.

Hence, as I said earlier, if you don't want to use Excel's methods of
calculating times, you'll have to write your own formulae to convert
the numbers to treat them as if they were times.
--
David Biddulph

JP Ronse wrote:
Hi Fontana,

Although I fully agree with the remarks everyone has made about hte
use of ":" for time, you can try to format the cells as "0000"
(without the quotes).

5 will be displayed as 0005
740 ... 0740
2355 ... 2355

As these are still numbers, calculations on it will not return an
error.
Please let me know if this was of any help.

Wkr,

JP

I keep a activity diary to appease the reviewer at the long term
disability co. I am used to recording time as military time, but I
cannot get Excel to
keep the leading zero unless I format the cell as text. And typing a
number
as text elicits an error which is a hassle to correct.
Click to expand...
Click to expand...
Click to expand...
 
Hi David,

Tnx for your explanation and the second example.

Wkr,

JP


David Biddulph said:
No, I don't think help assists much in this case.
Yes, I think you are right in regarding it as equivalent to an escape
character in this situation.
Another example of the same usage is =--TEXT(A2,"00\/00\/0000") to turn
07082009 into 07/08/2009 and thus into 07 Aug 2009 (or 08 Jul 2009,
depending on your Windows regional options, just as if you'd typed in
07/08/2009).
--
David Biddulph


JP Ronse said:
Hi David,

Tnx, why and when is the "\" needed in the format string?
The help is not explaining this and it looks to me as an escape character
as frequently used in UNIX commands.

Wkr,

JP

David Biddulph said:
The simple way of converting the 0040 to a time of 00:40 is to use
=--TEXT(A1,"00\:00") and format the cell as time.
--
David Biddulph

Hi David,

Very good remark, tnx. I was too much fixed on the format and not
enough on the time system.

Fontana,

To sum military time, this is the best formula I could think of.

=--(SUM(LEFT(A1:A11;1)*--(LEN(A1:A11)=3))+SUM(LEFT(A1:A11;2)*--(LEN(A1:A11)=4))+INT(SUM(--RIGHT(A1:A11;2))/60)&MOD(SUM(--RIGHT(A1:A11;2));60))

Has to be entered with ctrl-shift-enter.

Although you see always 4 digits, the length of the cell varies from 1
to 4.

(SUM(LEFT(A1:A11;1)*--(LEN(A1:A11)=3)) sums the hours having 1 digit,
e.g. 0740
SUM(LEFT(A1:A11;2)*--(LEN(A1:A11)=4)) sums the hours having 2 digits,
e.g. 1710
INT(SUM(--RIGHT(A1:A11;2))/60) sums the minutes and convert it to hours
MOD(SUM(--RIGHT(A1:A11;2));60)) returns the sum of the minutes less
then 1 hour

Use the & tho link the hours and minutes, the leading -- is to convert
this string back to a value.

Tested on the values below.

0740 07:40
1240 12:40
0530 05:30
0805 08:05
1710 17:10
1820 18:20
0006 00:06
1523 15:23
0933 09:33
0103 01:03
0023 00:23
9553 95:53


Wkr,

JP


"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
Calculations will not return an error, but will give the wrong result
if you wanted them to be treated as times. 0040 + 0040 would give
0080 instead of 0120.

Hence, as I said earlier, if you don't want to use Excel's methods of
calculating times, you'll have to write your own formulae to convert
the numbers to treat them as if they were times.
--
David Biddulph

JP Ronse wrote:
Hi Fontana,

Although I fully agree with the remarks everyone has made about hte
use of ":" for time, you can try to format the cells as "0000"
(without the quotes).

5 will be displayed as 0005
740 ... 0740
2355 ... 2355

As these are still numbers, calculations on it will not return an
error.
Please let me know if this was of any help.

Wkr,

JP

I keep a activity diary to appease the reviewer at the long term
disability co. I am used to recording time as military time, but I
cannot get Excel to
keep the leading zero unless I format the cell as text. And typing
a
number
as text elicits an error which is a hassle to correct.
Click to expand...
Click to expand...
Click to expand...
 
The simple way of converting the 0040 to a time of 00:40 is to use
=--TEXT(A1,"00\:00") and format the cell as time.
Click to expand...


Hi David ,

In the above formula what are these 2 dashes for...."=--TEXT"

Regards
Neeraj
 
Back
Top