MS Excel - who can figure this out?

[Skunkworks]

Its probably quite simple but I just cant work it out.

I have set the format of a cell as hhmm. I want to be able to enter times as 1035 for example, without the colon (10:35). When I type in the time (1035) it returns it as:

1902-10-31 00:00:00

however, if I put in 10:35 it then shows it as 1035! Have I made the question confusing enough? :)

/skunks

[spannersatcx]

to put 1035 in excel without the : type it as `1035 but I think the cell needs to be formatted as general or text. If the cell is formatted for time it has to have the colon to seperate it. excel can see a string (I think) and converts 1035 to the date this string represents. In this case 1902-10-31 00:00:00.

[This message has been edited by spannersatcx (edited 04 March 2001).]

[DreamCatcher]

Just put my log book onto a spreadsheet: had the same query.

You still have to type in the time with the colon, but it will appear in the cell as a straight e.g. 1035. Wait till you want to render 45 minutes as .75 of an hour. Now that IS fun!

------------------
If you're not on the edge, then you're taking up too much space.

[R O Tiree]

Dreamcatcher - if you want to have the result in hours and minutes (as opposed to hours.decimal hours) there is a really simple trick:

Open a new spreadsheet, type "23:10" in cell A1, "1:00" in A2. Excel, being clever and all, realises that you're typing in "times". Now click in A3, and type:

=SUM(A1:A2)

the answer is "00:10", which is not quite what you'd hoped for.

Right-click cell A3

Select "Format Cells..." from the pop-up menu

On the "Number" tab, select "Custom" at the bottom of the list

Type " [h]:mm " in the "Type" window, then click "OK"

You will find that the answer is now correct.

Ta Daaaaaaaah :)

This obviates the need for huge numbers of hidden columns, stripping out the minutes and hours, converting to decimal, adding up, then converting back to hh:mm format. Just let Excel do all that hard work.

[DreamCatcher]

ROT - thanks for that. Initially I was working in decimal time e.g. converting 45' to .75 hr. This was for the sole purpose of automatically calculating a running total for my flying costs (just a cross check).

Then I found that any decimal part relates to the decimal part of a day. Now if I fly 10:05 to 10:55 (50 mins e.g. L4-K4), then multiply that by 24 (a constant to bring 50 mins to a decimal of an hour, then multiply by the hourly rate, all is ticketty-boo!

------------------
If you're not on the edge, then you're taking up too much space.

[stickyb]

R O Tiree, do you know if your tip is applicable to Access as well? Have had endless trouble in the past with summing times in that system.
Rgds

[ExSimGuy]

If you use '1035 be warned that this becomes a text string only - you can't do any calculations on it!

That little "single quote" tells Excel to leave the text typed exacty as-is and not to try to do anything clever with it !

------------------
What goes around . . .
. . often lands better!