Time Forumla

[Sykovampyr]

Hi Guys,
I have decided that i am really rubbish with time formulas! I have a list of times that i need to assign to a 15 mintue period. The times are displayed as 01:00p, 01:01p, 01:12p etc for about a seven hour period. I want to split the times into 15 minute sections so the above would be 13:00-13:15 and have the forumla run up to 20:00, Can anyone help?

 

[jim may]

Do you mean somthing like this?

Excel Workbook
	A	B	C	D	E	F	G
1
2	13:00	<< First Formula on Row 2 - Subtract 2 from each Row() Number
3	13:15
4	13:30
5	13:45
6	14:00
7	14:15
8	14:30
9	14:45
10	15:00
11	15:15
12	15:30
13	15:45
14	16:00
15	16:15
16	16:30
17	16:45
18	17:00
19	17:15
20	17:30
21	17:45
22	18:00
23	18:15
24	18:30
25	18:45
26	19:00
27	19:15
28	19:30
29	19:45
30	20:00
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A2	=TIME(13, (ROW()-2)*15, 0)
A3	=TIME(13, (ROW()-2)*15, 0)
A4	=TIME(13, (ROW()-2)*15, 0)
A5	=TIME(13, (ROW()-2)*15, 0)
A6	=TIME(13, (ROW()-2)*15, 0)
A7	=TIME(13, (ROW()-2)*15, 0)
A8	=TIME(13, (ROW()-2)*15, 0)
A9	=TIME(13, (ROW()-2)*15, 0)
A10	=TIME(13, (ROW()-2)*15, 0)
A11	=TIME(13, (ROW()-2)*15, 0)
A12	=TIME(13, (ROW()-2)*15, 0)
A13	=TIME(13, (ROW()-2)*15, 0)
A14	=TIME(13, (ROW()-2)*15, 0)
A15	=TIME(13, (ROW()-2)*15, 0)
A16	=TIME(13, (ROW()-2)*15, 0)
A17	=TIME(13, (ROW()-2)*15, 0)
A18	=TIME(13, (ROW()-2)*15, 0)
A19	=TIME(13, (ROW()-2)*15, 0)
A20	=TIME(13, (ROW()-2)*15, 0)
A21	=TIME(13, (ROW()-2)*15, 0)
A22	=TIME(13, (ROW()-2)*15, 0)
A23	=TIME(13, (ROW()-2)*15, 0)
A24	=TIME(13, (ROW()-2)*15, 0)
A25	=TIME(13, (ROW()-2)*15, 0)
A26	=TIME(13, (ROW()-2)*15, 0)
A27	=TIME(13, (ROW()-2)*15, 0)
A28	=TIME(13, (ROW()-2)*15, 0)
A29	=TIME(13, (ROW()-2)*15, 0)
A30	=TIME(13, (ROW()-2)*15, 0)

 

[Sykovampyr]

Total Open time
2 27/09/2009 01:52P
3.4 27/09/2009 01:52P
3.4 27/09/2009 01:52P
3.2 27/09/2009 01:53P
3.4 27/09/2009 01:53P
1.5 27/09/2009 01:53P
1.7 27/09/2009 01:53P
1.7 27/09/2009 01:53P
2 27/09/2009 01:53P
2 27/09/2009 01:53P
3.7 27/09/2009 01:54P
3.7 27/09/2009 01:54P
3.7 27/09/2009 01:55P


Not sure if that would work, The data is laid out as above and need the column D to take the time data from Column C (01:52p) and have the output in D show 13:45-14:00 for example....

 

[Sykovampyr]

I am using 2007 BTW, If that makes a difference?