Vlookup and date format problem

  • LinkBack
  • Thread Tools
  • Rate This Thread
  • Display
  1. #1

    Registered User

    Join Date
    08-04-2005
    Posts
    27

    Unhappy Vlookup and date format problem

    Hi,

    I have yesterdays date in a spreadsheet which is obtained by the function =NOW()-1. It’s shown on the spreadsheet with a Date and a time and I didn’t want the time so I formatted the cell to only show the date.

    I then want to refer to this Cell with yesterdays date, which is in B2 in my main spreadsheet with a VLOOKUP as follows: =VLOOKUP(B2,’Historical Funds’!B4:C3000,2)

    The info in the Historical Funds sheet looks like this:
    A B C
    1
    2
    3
    4 05/08/2005 59.11
    5 04/08/2005 58.9
    6 03/08/2005 58.91

    The dates in Column B are UK formatted dates, without the time.

    Does anyone know what I have to do to first format my VLOOKUP search criteria correctly to handle dates? (maybe strip the time?) I know my Vlookup works, because when I search on simple strings instead of dates there is no problem getting my values from column C

    Any help would be appreciated!

  2. #2
    Bob Phillips

    Guest

    Re: Vlookup and date format problem

    Dazman,

    I think that you just want to use =TODAY()-1 instead of =NOW()-1

    HTH

    RP
    (remove nothere from the email address if mailing direct)

    “dazman” <dazman.1tcdaa_1123329921.3247@excelforum-nospam.com> wrote in
    message news:dazman.1tcdaa_1123329921.3247@excelforum-nospam.com…
    >
    > Hi,
    >
    > I have yesterdays date in a spreadsheet which is obtained by the
    > function =NOW()-1. It’s shown on the spreadsheet with a Date and a time
    > and I didn’t want the time so I formatted the cell to only show the
    > date.
    >
    > I then want to refer to this Cell with yesterdays date, which is in B2
    > in my main spreadsheet with a VLOOKUP as follows:
    > =VLOOKUP(B2,’Historical Funds’!B4:C3000,2)
    >
    > The info in the Historical Funds sheet looks like this:
    > A B C
    > 1
    > 2
    > 3
    > 4 05/08/2005 59.11
    > 5 04/08/2005 58.9
    > 6 03/08/2005 58.91
    >
    > The dates in Column B are UK formatted dates, without the time.
    >
    > Does anyone know what I have to do to first format my VLOOKUP search
    > criteria correctly to handle dates? (maybe strip the time?) I know my
    > Vlookup works, because when I search on simple strings instead of dates
    > there is no problem getting my values from column C
    >
    > Any help would be appreciated!
    >
    >
    > —
    > dazman
    > ————————————————————————
    > dazman’s Profile:

    http://www.excelforum.com/member.php…o&userid=25903
    > View this thread: http://www.excelforum.com/showthread…hreadid=393542
    >

  3. #3
    Ron Rosenfeld

    Guest

    Re: Vlookup and date format problem

    On Sat, 6 Aug 2005 06:49:13 -0500, dazman
    <dazman.1tcdaa_1123329921.3247@excelforum-nospam.com> wrote:

    >
    >Hi,
    >
    >I have yesterdays date in a spreadsheet which is obtained by the
    >function =NOW()-1. It’s shown on the spreadsheet with a Date and a time
    >and I didn’t want the time so I formatted the cell to only show the
    >date.
    >
    >I then want to refer to this Cell with yesterdays date, which is in B2
    >in my main spreadsheet with a VLOOKUP as follows:
    >=VLOOKUP(B2,’Historical Funds’!B4:C3000,2)
    >
    >The info in the Historical Funds sheet looks like this:
    > A B C
    >1
    >2
    >3
    >4 05/08/2005 59.11
    >5 04/08/2005 58.9
    >6 03/08/2005 58.91
    >
    >The dates in Column B are UK formatted dates, without the time.
    >
    >Does anyone know what I have to do to first format my VLOOKUP search
    >criteria correctly to handle dates? (maybe strip the time?) I know my
    >Vlookup works, because when I search on simple strings instead of dates
    >there is no problem getting my values from column C
    >
    >Any help would be appreciated!

    1. If the date/time in B2 is a “real” Excel date; and if the dates in your
    table are also real dates; then the formatting should not matter.

    2. Again, given the above, the presence of the time should not matter either.
    VLOOKUP should match to the date since that would be the largest value less
    than or equal to the contents of B2 (Date + time).

    The problem is elsewhere:

    Looking at your data, it seems as if your dates are sorted DESCENDING. With
    the VLOOKUP format you are using, dates need to be sorted ASCENDING.

    Assuming that both B2 and the dates in your table are “real” Excel dates (i.e.
    the underlying value is a serial number which is formatted to look like a date)
    then several solutions are possible.

    1. Sort your data so the date column is in ASCENDING order.
    2. Use the FALSE argument for range_lookup; and also either
    change your formula in B2 to =TODAY()-1 or, if you really need the
    time, change the value in your VLOOKUP function to INT(B2) (that will remove
    the time).

    With solution 2, your formula would be either:

    =VLOOKUP(B2,’Historical Funds’!B4:C3000,2,FALSE)

    or, if you do not change NOW to TODAY in B2, then:

    =VLOOKUP(INT(B2),’Historical Funds’!B4:C3000,2,FALSE)

    –ron

  4. #4

    Registered User

    Join Date
    08-04-2005
    Posts
    27

    Cool Thanks Guys, that did the trick!

    Thanks Guys, that did the trick!

Iklan
Vlookup and date format problem

SpreadSheetSpace SYNC: stop copy-pasting reports from ERPs, CRMs, Hadoop, etc. to Microsfot Excel

How many times do you run a report in SAP, Oracle, Hadoop, etc. and copy-paste such data to Excel? Do you do it daily, weekly, monthly? How many times do you have problems with missing data, wrong formats and styles, etc.? What if you discover a mistake and you have to repeat the copy-paste operation?

Connect Excel to your corporate systems using the SpreadSheetSpace Rest APIs.

SpreadSheetSpace SYNC solves all these issues because allows companies to create views of corporate data and allows Excel users to maintain their workbooks synchronized with the corporate data views in a secure and managed way from any location, thus enabling them to integrate the information stored in different IT platforms as well as to create customized analyses and presentations that automatically evolve with the corporate data.
More schematically:

  • Sync was created for companies that want to provide views of corporate…

Lihat pos aslinya 427 kata lagi

SpreadSheetSpace SYNC: stop copy-pasting reports from ERPs, CRMs, Hadoop, etc. to Microsfot Excel

Introduction to SQL Server System Stored Procedures

Technology News and Information by SeniorDBA

sqlserver2014

There are useful system stored procedures available to the users of SQL Server, and Rohatash Kumar has a recent article where he introduces users to some of these useful tools.

To Find All Database Object Information

exec sp_help

Output

Sp-help-in-SQL-Server

To Find Table Information

exec sp_help ’employee’

Output

Sp-help-with-table-name-in-SQL-Server

Lihat pos aslinya

Introduction to SQL Server System Stored Procedures

Important Soft Skills for the Entry-Level Data Professional

Technology News and Information by SeniorDBA

Managing your career means more than just learning the newest technology or keeping your existing technical skills sharp. It also includes the management of your soft skills. Soft skills are those skills that we sometimes take for granted, like the ability to communicate with other humans, or the ability to effectively solve business problems. When you are applying for a new job, or even looking for a promotion, these skills are also evaluated. You may be a great technical resource, but you usually have to have some of these soft skills so they non–technical people can effectively work with you to solve business problems.

In an interesting article by Tim Ford, he helps us understand what makes up the soft skills that businesses are looking for in their database professionals.

Last Friday on Twitter, I posed that question while mulling this over waiting to be picked up after getting stranded…

Lihat pos aslinya 213 kata lagi

Important Soft Skills for the Entry-Level Data Professional

10 Things Never To Say To Your Boss

Technology News and Information by SeniorDBA

Communication

There are just some things you should not say to your boss:

  1. At my last job we did it this way
  2. That’s not part of my job
  3. Well, I did my best
  4. I don’t know how
  5. What do I get from doing this
  6. I’ll leave/quit
  7. I just assumed that I was correct
  8. I don’t have a solution for this problem
  9. I can do that because I’m so smart
  10. I can’t do that

Sometimes you might feel like saying one of these thing when you can’t deliver a solution on time. I’ve been a developer for many years and I’ve been there. You have to know when and who you can say certain things to and it not damage your career or reputation. These types of comments will make your boss feel uncomfortable that you are in control, understand your assigned tasks, and are reliable in solving problems they way he/she wants them…

Lihat pos aslinya 1 kata lagi

10 Things Never To Say To Your Boss

BBM Bersubsidi Naik Malam Ini, 18 November 2014 Jam 00.00

MivecBlog.com

Yups, jam 9 malam tadi Presiden terpilih Joko Widodo secara resmi telah mengumumkan kenaikan harga BBM bersubsidi. Setelah berita kenaikan BBM ini sempat naik turun, akhirnya baik premium dan solar, masing-masing naik 2000 rupiah.

image

Untuk Premium naik menjadi 8.500 rupiah sedangkan solar naik menjadi 7.500 rupiah. Pengumuman yang sangat mendadak memang. Hal ini dikarenakan untuk menghindari kericuhan uang sering terjadi di SPBU saat kenaikan harga BBM sebelumnya.

Namun pengumuman mendadak inipun sepertinya sama saja. Dalam beberapa menit setelah pengumuman, seluruh SPBU di Batam telah dipenuhi oleh masyarakat yang mengantri BBM. Sebenarnya bensin mobil dan motor dirumah juga tinggal seperempat kapasitas tangki, tapi melihat antrinya jadi males ngisi 😀

Lihat pos aslinya 47 kata lagi

BBM Bersubsidi Naik Malam Ini, 18 November 2014 Jam 00.00

Siap-siap… Demo Besar-besaran Besok Kembali Digelar di Batam Selama 3 Hari

MivecBlog.com

Penetapan berapa UMK Batam tahun 2015 hingga hari ini masih belum diputuskan. Walaupun Walikota, Batam Ahmad Dahlan sudah memberikan angka yang diajukan ke Gubernur Kepri, namun para buruh/pekerja di Batam masih belum puas dengan angka yang keluar tersebut. Angka yang dikeluarkan Walikota Batam sebesar 2.664.302 rupiah masih dianggap terlalu jauh dibandingkan dengan tuntutan buruh sebesar 3,3 juta rupiah.

Menurut Ahmad Dahlan, angka yang dikeluarkan tersebut merupakan angka netral dimana berada di tengah-tengah antara tuntutan buruh dan keinginan pengusaha/Apindo (Asosiasi Pengusaha Indonesia) yang berada di angka 2,1 juta rupiah.

Demo Buruh Batam Demo Buruh Batam

Terkait dengan rekomendasi dari Walikota tersebut, pihak serikat pekerja jelas menolak karena terlalu jauh dari harapan, padahal biaya hidup di Batam semakin naik, apalagi dengan rencanan kenaikan BBM dalam waktu dekat. Dan pihak serikat pekerja yang ada di Batam-pun memutuskan untuk mengerahkan massa selama 3 hari mulai besok pagi (18 Nov 2014). Demo ini bertujuan untuk:

Lihat pos aslinya 77 kata lagi

Siap-siap… Demo Besar-besaran Besok Kembali Digelar di Batam Selama 3 Hari