# Help with simple fucntion

#### abberyfarm

##### Well-known Member
Hi there,

I have date and time in column A. Would anybody know a function to put numbers in column B that increase by 1 in each 15 minute block?

Here is an example.

Appreciate any help

Excel Workbook
AB
1Date/Time*
201/09/2014 00:00:100
301/09/2014 00:01:070
401/09/2014 00:11:430
501/09/2014 00:12:410
601/09/2014 00:13:390
701/09/2014 00:14:360
801/09/2014 00:15:341
901/09/2014 00:16:321
1001/09/2014 00:17:301
1101/09/2014 00:18:271
1201/09/2014 00:26:101
1301/09/2014 00:27:071
1401/09/2014 00:28:051
1501/09/2014 00:29:031
1601/09/2014 00:30:012
1701/09/2014 00:30:582
1801/09/2014 00:31:562
1901/09/2014 00:32:542
2001/09/2014 00:43:302
2101/09/2014 00:44:272
2201/09/2014 00:45:253
2301/09/2014 00:46:233
2401/09/2014 00:56:583
2501/09/2014 00:57:563
2601/09/2014 00:58:543
2701/09/2014 00:59:523
2801/09/2014 01:00:494
2901/09/2014 01:05:394
3001/09/2014 01:06:374
Sheet1

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

any ideas?

#### ParamRay

##### Well-known Member
.
.

=4*hour(a2)+rounddown(minute(a2)/15,0)

#### abberyfarm

##### Well-known Member

This formula seems to maxeout at 95 when the date changes and then reset to 0,1,2...etc

Would you know a workaround to keep it increasing even when the date changes?

Many thanks

Last edited:

#### ParamRay

##### Well-known Member

This formula seems to maxeout at 95 when the date changes and then reset to 0,1,2...etc

Would you know a workaround to keep it increasing even when the date changes?

Many thanks

Yes, try the following formula instead. Note that I have used 01/09/2014 as the starting point for the count, but you can change this as required:

=24*4*(ROUNDDOWN(A2,0)-DATE(2014,9,1))+(4*HOUR(A2)+ROUNDDOWN(MINUTE(A2)/15,0))

Replies
8
Views
565
Replies
2
Views
316
Replies
4
Views
392
Replies
5
Views
586
Replies
1
Views
221

1,191,282
Messages
5,985,740
Members
439,978
Latest member
Mr930R

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back