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

[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.

 

[Gary''s Student]

Format the cell(s) Custom hh:mm

Then enter data like:
07:30

 

[Bernard Liengme]

I think Gary meant : hhmm without the colon
best wishes

 

[David Biddulph]

In my view it is preferable to include the colon in the format, and thus
make it clearer that it is indeed a time.

 

[fontana]

I do not want to use a colon. When I tried hhmm, I got date and 0000 when I
entered 0945.

 

[fontana]

I tried this, but when I entered 0945, the display showed a date and 0000.

 

[David Biddulph]

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.

 

[Bernard Liengme]

Agreed, but who am I to fight the generals?
I made corporal in the Swiss Army

 

[JP Ronse]

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

 

[David Biddulph]

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.

 

[Gord Dibben]

If you don't want to bother typing the colon you can use event code.

See Chip Pearson's site for code.

http://www.cpearson.com/excel/DateTimeEntry.htm


Gord Dibben MS Excel MVP

 

[JP Ronse]

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]

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.

 

[JP Ronse]

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]

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

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

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.

 

[JP Ronse]

Hi David,

Tnx for your explanation and the second example.

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

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

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.

 

[Neeraj Gupta]

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 ,

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

Regards
Neeraj

 

[David Biddulph]

Double unary minus:
a.. http://www.mcgimpsey.com/excel/formulae/doubleneg.html
b.. http://xldynamic.com/source/xld.SUMPRODUCT.html
--
David Biddulph

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 ,

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

Regards
Neeraj