+ Reply to Thread
Results 1 to 9 of 9

Formulas and Dates

  1. #1
    Registered User
    Join Date
    04-23-2012
    Location
    Davie, FL
    MS-Off Ver
    Excel 2013
    Posts
    83

    Formulas and Dates

    The column below shows enrollment dates. Unfortunately, I dont have ACTUAL dates. These only count how many days from TODAY the enrollment was done. Is it possible to create a formula that will actually give me a DATE? I need to calculate the one closest to TODAY.

    I guess in English it would be something like: TODAY MINUS 1 HOUR = TODAY'S DATE...... or

    TODAY MINUS 38 DAYS = APRIL 19, 2013


    Age

    1 Hour
    2 Hours
    38 Days
    2 Hours
    4 Hours
    29 Days
    29 Days
    34 Days
    42 Days
    48 Days
    49 Days
    56 Days
    59 Days
    73 Days
    77 Days
    78 Days
    30 Days
    < 1 Hour

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Formulas and Dates

    You show a column of data. re they formulas or typed values?
    Gary's Student

  3. #3
    Registered User
    Join Date
    04-23-2012
    Location
    Davie, FL
    MS-Off Ver
    Excel 2013
    Posts
    83

    Re: Formulas and Dates

    its not formulas.. Just raw data that I pulled from a report.

  4. #4
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Formulas and Dates

    Try this formula:

    =IF(SUBSTITUTE(A1,"Hour","")=A1,TODAY()-SUBSTITUTE(A1," Days",""),"less than 1 day")

  5. #5
    Registered User
    Join Date
    04-23-2012
    Location
    Davie, FL
    MS-Off Ver
    Excel 2013
    Posts
    83

    Re: Formulas and Dates

    most of them worked.. but the ones that had 1 Hour came out as "less than 1 day" instead of today's date. There was also another one that said 1 Day and that one came out #VALUE!.

    I do have one more that says < 1 Hour = How do I get a date for that one?

    By the way, thank you so much for all your help
    Last edited by trosasco; 05-23-2013 at 05:15 PM.

  6. #6
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Formulas and Dates

    This will work better:

    =IF(SUBSTITUTE(A1,"Hour","")=A1,TODAY()-LEFT(A1,FIND(" ",A1)),TODAY())

  7. #7
    Registered User
    Join Date
    04-23-2012
    Location
    Davie, FL
    MS-Off Ver
    Excel 2013
    Posts
    83

    Re: Formulas and Dates

    You are AWESOME!!. That worked perfectly.. Now, the second part of this report is to look for the previous date out of that list.. This is what my formula looks like: See attached on L2.. It should be 4/25/2013 but my formula is coming up with 5/17/2013. I need to count the previous to last date out of the Active Clients only. And then in another formula, i need to count how many of those dates there are (which in this case, there would be 2)
    Book3.xlsx
    Last edited by trosasco; 05-24-2013 at 02:45 PM.

  8. #8
    Registered User
    Join Date
    04-23-2012
    Location
    Davie, FL
    MS-Off Ver
    Excel 2013
    Posts
    83

    Re: Formulas and Dates

    I also need to count how many of the Active Clients only are 5/23/2013. This will go in another report.

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Formulas and Dates

    Start a new thread with this new question

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1