MS Excel - who can figure this out?
Guest
Posts: n/a
MS Excel - who can figure this out?
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
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
Guest
Posts: n/a
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).]
[This message has been edited by spannersatcx (edited 04 March 2001).]
Guest
Posts: n/a
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.
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.
Guest
Posts: n/a
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.
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.
Guest
Posts: n/a
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.
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.
Guest
Posts: n/a
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!
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!
