+ Reply to Thread
Results 1 to 13 of 13

Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by date

  1. 04-08-2015, 09:12 AM #1
    Rossovich
    Rossovich is offline
    Registered User
    Join Date
    03-06-2012
    Location
    Thunder Bay, ON
    MS-Off Ver
    Excel 2007
    Posts
    19

    Post Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by date

    I am building a calendar that will allow 4 departments to see how much vacation allowance is remaining based on the month the choose.
    For the attached example, the second tab has the data the first tab will pull.
    The end result that I am looking for is if they choose April as the month, it auto populates the days where they should be.
    I then want the data from the second tab to populate based on the day in question.
    I have attempted to use concatenate, but I still receive a N/A as a result in the vlookup
    Thank you for your time and help in this matter, it is greatly apprecaited.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-08-2015, 09:23 AM #2
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    I don't think the problem is the date being in text.

    Try this in F6 and copy across.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 04-08-2015, 09:26 AM #3
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    F6=iferror(vlookup(datevalue(f5&" "&$c$3&" "&$d$3),indirect($a6),2,false),"")
    f7=iferror(vlookup(datevalue(f5&" "&$c$3&" "&$d$3),indirect($a7),2,false),"")
    f8=iferror(vlookup(datevalue(f5&" "&$c$3&" "&$d$3),indirect($a8),2,false),"")
    f9=iferror(vlookup(datevalue(f5&" "&$c$3&" "&$d$3),indirect($a9),2,false),"")
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  4. 04-08-2015, 09:32 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    Try this out. Completed for Billing...
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  5. 04-08-2015, 10:07 AM #5
    Rossovich
    Rossovich is offline
    Registered User
    Join Date
    03-06-2012
    Location
    Thunder Bay, ON
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    Thank you to all.
    Glenn this has provided me with exactly what I needed.
    One question, when I cahnge the month to August the days now show a #Value! it only happens in this month.
    Reply With QuoteRegister To Reply
  6. 04-08-2015, 10:15 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    It's not the INDEX frmula. It's the date calculation in the row above it. It's something to do with August starting on a Saturday. Why did you use 16 as the return type in the weekday function in E2??
    Reply With QuoteRegister To Reply
  7. 04-08-2015, 10:20 AM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    Change e2 TO =WEEKDAY(E1,1)
    Change C1 to =IF($E2=COLUMN()-2,1,"")
    Change D1 to =IF(C5<>"",C5+1,IF($E2=COLUMN()-2,1,"")) and copy across to I5.

    That seems to fix it...
    Reply With QuoteRegister To Reply
  8. 04-08-2015, 10:25 AM #8
    Rossovich
    Rossovich is offline
    Registered User
    Join Date
    03-06-2012
    Location
    Thunder Bay, ON
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    I tried every other number and 16 was the only one that worked for every month, but August. If you have a different way please feel free to enlighten me. :-)
    Here is what I have for the Weekday function:
    1 or omitted
    Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
    2
    Numbers 1 (Monday) through 7 (Sunday).
    3
    Numbers 0 (Monday) through 6 (Sunday).
    11
    Numbers 1 (Monday) through 7 (Sunday).
    12
    Numbers 1 (Tuesday) through 7 (Monday).
    13
    Numbers 1 (Wednesday) through 7 (Tuesday).
    14
    Numbers 1 (Thursday) through 7 (Wednesday).
    15
    Numbers 1 (Friday) through 7 (Thursday).
    16
    Numbers 1 (Saturday) through 7 (Friday).
    17
    Numbers 1 (Sunday) through 7 (Saturday).
    Reply With QuoteRegister To Reply
  9. 04-08-2015, 10:29 AM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    Yea. I've now been through all the months (for 2015) and the amendments suggested above all are OK
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 04-08-2015, 10:40 AM #10
    Rossovich
    Rossovich is offline
    Registered User
    Join Date
    03-06-2012
    Location
    Thunder Bay, ON
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    Should C1 and D1 be C% and D5?
    Reply With QuoteRegister To Reply
  11. 04-08-2015, 10:42 AM #11
    Rossovich
    Rossovich is offline
    Registered User
    Join Date
    03-06-2012
    Location
    Thunder Bay, ON
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    Sorry, I saw reply after hitting post.

    Thank you so much Glenn.!!!!
    Everything is working perfectly!
    Last edited by Rossovich; 04-08-2015 at 10:44 AM.
    Reply With QuoteRegister To Reply
  12. 04-08-2015, 10:42 AM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    Nope (I think...) See attachment in post above. You have me "worried" now...
    Reply With QuoteRegister To Reply
  13. 04-08-2015, 10:43 AM #13
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: Converting Date Text to Number format to use in a VLOOKUP to polpulate data based by d

    Dohh.. Talking through my @ss. Of course they shoud be 5....
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Converting from the 1904 date format to the 1900 date format without losing data
    By Patty McJorst in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-01-2020, 12:53 PM
  2. [SOLVED] Formula to count Date without converting data to Text Format
    By syed82 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-30-2014, 12:21 AM
  3. Need assistance converting times stored in date format to number format.
    By halabis in forum Excel General
    Replies: 5
    Last Post: 04-01-2014, 09:37 AM
  4. Converting date text to date format
    By reggie1000 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-01-2009, 06:10 AM
  5. [SOLVED] [SOLVED] Converting number or text to a Date Format
    By samhain in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-17-2005, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1