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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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