DataTime in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

DataTime in excel
I have a DateTime column in A. How do I get this two values?
1. only date in column B
2. Time range in column C like (0-1 o’clock , 1-2 o’clock, 2-3 o’clock …)
Thanks,
 
Jim,

You can do this with only formatting, or you can use formulas to extract the date, and the
time. For the formatting approach, in B2 and in C2, put this formula: = A2. COpy down.
Format column B: Format - Cells - Number - Date, then pick the date format you want. For
column C: Format - Cells - Number - Time, and pick the time format you want. Note that the
B and C columns still yield the entire date-time number, but the formatting shows only the
part you want. Any formulas that refer to these columns will get the entire data-time
number, but the formatting will cause only the date, or the time, as you want, to show.

For the formula approach, put this in B2: =INT(A2). Format column B for the date format you
want as above. The formula will yield only the date part, not the time (fractional) part.
Then put this in C2: =MOD(A2,1). Format only for time, as above. You would use this
approach if other formulas refer to these columns, and should get only the desired date or
time part.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Jim,

I didn't read the column C part of your post carefully enough. Put this formula in C2, and
copy down:

FOr 24-hour time:
=INT(MOD(A2,1)*24) & " - " &INT(MOD(A2,1)*24) + 1 & " O'Clock"

Or for 12-hour time
=MOD(INT(MOD(A2,1)*24),12) & " - " & MOD(INT(MOD(A2,1)*24) + 1,12) & " O'Clock"
 
Back
Top