Current Date Comparison / Cell Range Retrival

arshadmohamed

New Member
Joined
Jan 18, 2005
Messages
18
Hiiiii :)

I have some data entered in a Excel like this..

Each time i open this work book i want the current system date datas to be appearing in some particular cell like in "H6, H7, H8 & H9"

issit possible to compare and check for current system dates?

If so how will i make the assigned time data's appear in some plc in the sheet?

Date Time-1 Time-2 Time-3

01-01-05 10:01 23:06 8:15
02-01-05 11:01 0:06 9:15
03-01-05 12:01 1:06 10:15
04-01-05 13:01 2:06 11:15
05-01-05 14:01 3:06 12:15
06-01-05 15:01 4:06 13:15
07-01-05 16:01 5:06 14:15
08-01-05 17:01 6:06 15:15
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
How about this:
Press Alt-F11 to bring up the VBA window.
In the left pane, double-click on the "ThisWorkbook" icon.
Paste this in:

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Range("A1").Select
Do Until ActiveCell.Text = ""
If Format(Now(), "dd/mm/yy") = Format(ActiveCell.Value, "dd/mm/yy") Then
ActiveCell.End(xlToRight).Offset(0, 1).Value = Format(Now(), "hh:mm")
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
ActiveCell.Value = Format(Now(), "dd/mm/yy")
ActiveCell.Offset(0, 1).Value = Format(Now(), "hh:mm")
End Sub
 

arshadmohamed

New Member
Joined
Jan 18, 2005
Messages
18
I tried the code which was given,

The result is , i see the current date appearing in the Cell A1 and the Current time on A2..

What i wish to retrive is..

I want the data corresponding to the current date to be appearing on Cell A1,A2 and A3

sope it wud require to compare the current date with the dates in the data entered and produce the current date data..

Is that possible?
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
What you ask for sounds possible,

Post your worksheet (use Colo's HTML Maker at the bottom of this sheet) and provide a more detailed explanation.
 

arshadmohamed

New Member
Joined
Jan 18, 2005
Messages
18

ADVERTISEMENT

Date Time-1 Time-2 Time-3

01-01-05 10:01 23:06 8:15
02-01-05 11:01 0:06 9:15
03-01-05 12:01 1:06 10:15
04-01-05 13:01 2:06 11:15
05-01-05 14:01 3:06 12:15
06-01-05 15:01 4:06 13:15
07-01-05 16:01 5:06 14:15
08-01-05 17:01 6:06 15:15
09-01-05 18:01 7:06 16:15
10-01-05 19:01 8:06 17:15
[/code]
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
I am still not sure I understand, but try this (if I read it correctly):

I assume that you have your data in columns A:D
In a cell (say H1), type =today()
In the next cell (I1), type
=vlookup(h1,a:d,2,false)
In J1:
=vlookup(h1,a:d,3,false)
In K1:
=vlookup(h1,a:d,4,false)

Is that what you want?
 

arshadmohamed

New Member
Joined
Jan 18, 2005
Messages
18

ADVERTISEMENT

iam really sorry i dono how to paste the excel file on this board..

How to..

Any way my table is not complicated at all..

it has like this

DATEEEE.......TIME-1....TIME-2....TIME-3

01-01-05.....01:22....03:25....07:50
02-01-05.....03:25....02:20....03:20
03-01-05.....01:22....04:20....04:50
24-01-05.....05.33....04:40....05:55

i want the result in cell A1 A2 A3

which must be the data corresponding to the current date like

24-01-05.....05.33....04:40....05:55
 

arshadmohamed

New Member
Joined
Jan 18, 2005
Messages
18
Sir, i tried the way u mentioned..

But i guess that was not it..

i juz showed above the nature of my table ..

It basically requires to compare the Current date with the Dates in Column A

and if there is a match, the 3 time datas corresponding to that particular date have to be published as the "Out Put"

seems simple, but dono how to.. Comparing using IF statement and Today() can be done, but how to gt the data frm 3 diff cells?
 

arshadmohamed

New Member
Joined
Jan 18, 2005
Messages
18
Book13.xls
CDEF
9DateTime-1Time-2Time-3
1019-01-054:0117:062:15
1120-01-055:0118:063:15
1221-01-056:0119:064:15
1322-01-057:0120:065:15
1423-01-058:0121:066:15
1524-01-059:0122:067:15
1625-01-0510:0123:068:15
1726-01-0511:010:069:15
1827-01-0512:011:0610:15
1928-01-0513:012:0611:15
Sheet1


Now i can paste it on the board.. How to compare the Date column with the current system date and publish the corresponding current system dates data's Time1 Time2 Time3 to some cells in the same worksheet?
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi Arshad,

Have a look..
Book2
ABCDEFGHI
1DateTime-1Time-2Time-3CurrentDateTime-1Time-2Time-3
219/01/20054:0117:062:1524/01/20059:0122:067:15
320/01/20055:0118:063:15
421/01/20056:0119:064:15
522/01/20057:0120:065:15
623/01/20058:0121:066:15
724/01/20059:0122:067:15
825/01/200510:0123:068:15
926/01/200511:010:069:15
1027/01/200512:011:0610:15
1128/01/200513:012:0611:15
Sheet1
 

Forum statistics

Threads
1,147,623
Messages
5,742,209
Members
423,714
Latest member
ftp2jz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top