Formula to calculate time

[accelerator]

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?

[Niek Otten]

=A1+(8.5/24)

Time in Excel is stored as a fraction of a day

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"accelerator" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

[accelerator]

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

[Terry K]

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

[accelerator]

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!

[Tom Ogilvy]

an alternative is to add a time value

=A1+Timevalue("8:30")

--
Regards,
Tom Ogilvy

"accelerator" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

[accelerator]

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

[Gary Keramidas]

try this

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

--


Gary


"accelerator" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[accelerator]

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?