In my spreadsheet, I have column A defined as format "time". There, I
insert values like:

0:00
13:20
7:30

etc.

In column B I have the formulae:

=A1+8.5
=A2+8.5

etc. (I want to add 8 1/2 hours to the start time.)

However, I'm not getting what I would expect. I would expect to see:

0:00 8:30
13:20 23:50
7:30 16:00

But instead I'm seeing:

0:00 12:00
13:20 1:20
7:30 19:30


What am I doing wrong?

=A1+(8.5/24)

Time in Excel is stored as a fraction of a day

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"accelerator" wrote in message
oups.com...
In my spreadsheet, I have column A defined as format "time". There, I
insert values like:

0:00
13:20
7:30

etc.

In column B I have the formulae:

=A1+8.5
=A2+8.5

etc. (I want to add 8 1/2 hours to the start time.)

However, I'm not getting what I would expect. I would expect to see:

0:00 8:30
13:20 23:50
7:30 16:00

But instead I'm seeing:

0:00 12:00
13:20 1:20
7:30 19:30


What am I doing wrong?

Thanks! I wish I could have found that easily in the help (some
examples would be nice)

Hi accelerator,
Try this site out.
http://www.cpearson.com/excel/datearith.htm
This does work for sure.
Terry

I also noticed, that although I set the format to UTC (e.g. 13:30),
when I enter 12:00 it assumes that it is midnight and not mid-day.
When I enter 12:00 it displays 0:00!

an alternative is to add a time value

=A1+Timevalue("8:30")

--
Regards,
Tom Ogilvy

"accelerator" wrote in message
oups.com...
In my spreadsheet, I have column A defined as format "time". There, I
insert values like:

0:00
13:20
7:30

etc.

In column B I have the formulae:

=A1+8.5
=A2+8.5

etc. (I want to add 8 1/2 hours to the start time.)

However, I'm not getting what I would expect. I would expect to see:

0:00 8:30
13:20 23:50
7:30 16:00

But instead I'm seeing:

0:00 12:00
13:20 1:20
7:30 19:30


What am I doing wrong?

I couldn't duplicate that behavior in xl2003. In tools
=Options=Transition, do you have anything selected in the checkboxes on
that dialog. If so, try unchecking them.

--
Regards,
Tom Ogilvy


"accelerator" wrote in message
oups.com...
I also noticed, that although I set the format to UTC (e.g. 13:30),
when I enter 12:00 it assumes that it is midnight and not mid-day.
When I enter 12:00 it displays 0:00!

OK -- Now how can I place a date in a cell as text but with a specific
format?

e.g.

cell a1, formatted as date, currently displays 21/10/05

I want to place (via vba) this date as text in cell a2, but I want it
to look like

21 Oct 2005

or

October 21, 2005

try this

With Range("a1")
..NumberFormat = "MMMM DD, YYYY"
End With

--


Gary


"accelerator" wrote in message
oups.com...
OK -- Now how can I place a date in a cell as text but with a specific
format?

e.g.

cell a1, formatted as date, currently displays 21/10/05

I want to place (via vba) this date as text in cell a2, but I want it
to look like

21 Oct 2005

or

October 21, 2005

That didn't do it. I used the code:

Sub test()
Range("b1") = Range("a1").Value
With Range("b1")
..NumberFormat = "MMMM DD, YYYY"
End With

but when I changed the format of the cell to text, I see:

38646


How can I convert the date to the above format, then copy the results,
*as text*, to another cell, formatted as text?