View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default Time & Date Formulas

Karen wrote...
....
Also, I have to enter a time in J2 (example: 10:56am) - (Not sure if this
matters, but it must be entered without the space and the am or pm in
lowercase) . . .


So it has to appear like that in cell J2? If so, you need trickery to
achieve your indicated format. Specifically, you'd need the custom
number format

[<0.5]hh:mm"am";[<1]hh:mm"pm";;

This will only display times, i.e., date values = 0 and < 1.

. . . I want a formula to populate K2 in increments of 30 minutes. So,
when I enter 10:56am, I want K2 to display 10:30a - 11a (Exactly like that) -
If I enter 1:15am, I want it to display, 1am - 1:30am)


Your specs are inconsistent: 10:30a - 11a without m but 1am - 1:30am
with m.

K2:
=SUBSTITUTE(SUBSTITUTE(TEXT(INT(J2*48)/48,"h:mm& - ")
&TEXT(INT(J2*48+1)/48,"h:mm&"),":00",""),"&",IF(J2<0.5,"am","pm"))