Date and Time in a cell

  • Thread starter Thread starter fluff
  • Start date Start date
F

fluff

Hello --

I have a cell formatted as date and time. Is there a way to extract
just the time portion of that cell to another cell?

Thanks!
 
Don,
I have tried that method and if I do calculations with the result to another
date (subtract one from the other) the time seems to be hidden in the
background and I get a decimal answer. I have been doing the =text(a1,
"mm/dd/yy") is there a better (quicker) way?

Thanks for your time.

Lomax


Don Guillett said:
sure. Just format it as time.
Click to expand...
 
Why not just =a1 and then format that cell as desired.

Clyde Lomax said:
Don,
I have tried that method and if I do calculations with the result to another
date (subtract one from the other) the time seems to be hidden in the
background and I get a decimal answer. I have been doing the =text(a1,
"mm/dd/yy") is there a better (quicker) way?

Thanks for your time.

Lomax
Click to expand...
 
Formatting a cell merely changes what is displayed. It doesn't change the
underlying value in the cell, and it's this underlying value that is used in
calculations referencing the cell.

Dates in Excel are stored as the number of days since 31st Dec 1899 (i.e.
1st Jan 1900 is 1).
Times are the decimal part of these numbers.
So, today (7th Nov 2003) is stored as 37932 .
6am today (i.e. a quarter of the way through today) is therefore stored as
37932.25 .
To extract just the time for use in calculations from a date and time in A1,
you can use
=MOD(A1,1)
To get the actual number of hours, you multiply this by 24 (as there are 24
hours in a day), and so on.

The complication in all of this is that Excel has a habit of automatically
changing the formatting of a cell to what it thinks you want! Thus, if you
type a date, Excel will change to a date format. If you type the above
formula when there is already data in A1, Excel will change the format to
date/time and you will see 1/00/00 6:00 (or similar, depending on your
regional date settings). If it isn't what you want (e.g. you wanted to see
just the time 6:00 or the value 0.25), just change the formatting to what
you want. The best way of seeing the underlying value is to change the
format to General.

The important point is that there is a world of difference (as far as
calculations are concerned) between a cell containing 37932.25 and one
containing 0.25, even though both would appear as 6:00 (or similar) if
formatted as time.
 
Back
Top