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

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.

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

Format the cell(s) Custom hh:mm

Then enter data like:
07:30
--
Gary''s Student - gsnu200860


"fontana" wrote:

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.

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

I think Gary meant : hhmm without the colon
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"fontana" wrote in message
...
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.

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

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

"Bernard Liengme" wrote in message
...
I think Gary meant : hhmm without the colon
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"fontana" wrote in message
...
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.

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

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

"Gary''s Student" wrote:

Format the cell(s) Custom hh:mm

Then enter data like:
07:30
--
Gary''s Student - gsnu200860


"fontana" wrote:

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.

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

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

"Bernard Liengme" wrote:

I think Gary meant : hhmm without the colon
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"fontana" wrote in message
...
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.

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

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.
--
David Biddulph

"fontana" wrote in message
...
I tried this, but when I entered 0945, the display showed a date and 0000.

"Bernard Liengme" wrote:

I think Gary meant : hhmm without the colon
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"fontana" wrote in message
...
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.

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

Agreed, but who am I to fight the generals?
I made corporal in the Swiss Army
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
In my view it is preferable to include the colon in the format, and thus
make it clearer that it is indeed a time.
--
David Biddulph

"Bernard Liengme" wrote in message
...
I think Gary meant : hhmm without the colon
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"fontana" wrote in message
...
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.

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

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

"fontana" wrote in message
...
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.

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

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

"fontana" wrote in message
...
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.

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

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

On Wed, 12 Aug 2009 08:45:02 -0700, fontana
wrote:

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

"Gary''s Student" wrote:

Format the cell(s) Custom hh:mm

Then enter data like:
07:30
--
Gary''s Student - gsnu200860


"fontana" wrote:

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.

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

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

"fontana" wrote in message
...
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.

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

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" wrote in message
...
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

"fontana" wrote in message
...
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.

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

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" <groups [at] biddulph.org.uk wrote in message
...
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" wrote in message
...
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

"fontana" wrote in message
...
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.

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

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" wrote in message
...
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" <groups [at] biddulph.org.uk wrote in message
...
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" wrote in message
...
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

"fontana" wrote in message
...
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.

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

Hi David,

Tnx for your explanation and the second example.

Wkr,

JP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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" wrote in message
...
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" <groups [at] biddulph.org.uk wrote in message
...
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" wrote in message
...
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

"fontana" wrote in message
...
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.

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

On Aug 13, 4:29*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
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 ,

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

Regards
Neeraj

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

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

"Neeraj Gupta" wrote in message
...
On Aug 13, 4:29 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
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 ,

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

Regards
Neeraj