# time interval

#### earp_

##### Active Member
Hi I have this problem,
in cell A2 of sheet1 there is a timestamp in this format
12:00:00 PM
When cell A2 of sheet1 is between 12:00:00 and 12:59:59 I want to write 'hello' into another cell: A4 of sheet2

How does it work?

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### Richard Schollar

##### MrExcel MVP
Hi

Perhaps:

=IF(HOUR(Sheet1!A2)=12,"Hello","")

in A4 of Sheet2

#### Jonmo1

##### MrExcel MVP
Perhaps..

=IF(HOUR(Sheet1!A2)=12,"hello","")

HTH

#### earp_

##### Active Member
the last question...what if I need also an interval from 12.20 to 12.30 ?

#### Jonmo1

##### MrExcel MVP
=IF(AND(Sheet1!A2>="12:20:00"*1,Sheet1!A2<="12:30:00"*1),"Hello","")

#### earp_

##### Active Member
Sorry guys but i wrote 'hello' because I though would be easier...but it starts to complicate my point of view.
that work perfect:
=IF(AND(Sheet1!A2>="12:20:00"*1,Sheet1!A2<="12:30:00"*1),"Hello","")
but instead writing hello and I want to copy a value of cell A1 of sheet1 into Cell A3 of sheet2 in that interval, can you tell me how using that way?

#### Jonmo1

##### MrExcel MVP
Replace "Hello" with Sheet1!A1

great thanks

#### earp_

##### Active Member
There is something wrong.
Can you tell me what?

Dim LastR As Range
With ThisWorkbook
Set LastR = .Sheets("sheet2").Range("A1")
If Not IsEmpty(LastR) Then _
Set LastR = .Sheets("sheet2").Range("A" & Rows.Count).End(xlUp)(2)
LastR = IF(AND(Sheet1!A2>="15:37:00"*1,Sheet1!A2<="15:38:00"*1),"Hello","")
End With
I did that so it will start to fill my values from A1 otherwise it started from A2
but the error is
LastR = IF(AND(Sheet1!A2>="15:37:00"*1,Sheet1!A2<="15:38:00"*1),"Hello","")

#### Jonmo1

##### MrExcel MVP
Are you trying to put that formula in the cell with VBA Code?

LastR.Formula = "=IF(AND(Sheet1!A2>=""15:37:00""*1,Sheet1!A2<=""15:38:00""*1),""Hello"","""")"

Replies
4
Views
308
Replies
3
Views
287
Replies
2
Views
520
Replies
5
Views
490
Replies
3
Views
212

1,190,745
Messages
5,982,713
Members
439,791
Latest member
NwaTech_

### 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