+ Reply to Thread
Results 1 to 9 of 9

DATE AND YEAR Functions

  1. #1
    Registered User
    Join Date
    11-05-2017
    Location
    united states
    MS-Off Ver
    2010
    Posts
    6

    DATE AND YEAR Functions

    Hi guys so I have started this assignment based off of what I understood. However, I know my approach is wrong. Can someone help me out please.

    Thanks,
    Mahnoor Ibadat

    SOLVED
    Attached Files Attached Files
    Last edited by mibadat; 11-06-2017 at 09:18 PM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,136

    Re: DATE AND YEAR Functions

    welcome to the forum. you could use a TODAY() formula somewhere say in cell E1 so that you don't have to keep recalculating it in column C and D:
    =TODAY()

    then in C2, try:
    =DATE(YEAR($E$1),MONTH(B2),DAY(B2))

    in D2, try:
    =IF($E$1>C2,"Passed",C2-TODAY())

    ps: please mark your thread as "Solved" once it has answered your question. you may look at my signature on several tips on how to mark it "Solved", some tips i compiled, and how to give a good eg in order to get quick answers.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    11-05-2017
    Location
    united states
    MS-Off Ver
    2010
    Posts
    6

    Re: DATE AND YEAR Functions

    Thanks so much

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,336

    Re: DATE AND YEAR Functions

    Another way.
    With current year in E1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and next year in F1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Array enter this in D2 and fill down.
    If you arenít familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.




    D
    E
    F
    G
    1
    Days Left
    2017
    2018
    11/6/2017
    2
    39
    3
    315
    4
    196
    5
    137
    Dave

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,129

    Re: DATE AND YEAR Functions

    What if DOB equals 29th Feb 1996?
    Last edited by bebo021999; 11-06-2017 at 10:21 PM. Reason: change the year

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,336

    Re: DATE AND YEAR Functions

    DOB 2/29/1994 is not a valid date. 1994 is not a leap year.

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: DATE AND YEAR Functions

    Quote Originally Posted by bebo021999 View Post
    What if DOB equals 29th Feb 1996?
    That means person has birthday every 4 years not for every year
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,129

    Re: DATE AND YEAR Functions

    Quote Originally Posted by FlameRetired View Post
    DOB 2/29/1994 is not a valid date. 1994 is not a leap year.
    I edited right after post
    I think there are 2 options for leap year:
    Use EDATE(...) returns 28 Feb
    Use DATE(...) returns 1 March

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,336

    Re: DATE AND YEAR Functions

    Aha. I hadn't considered that. Mine won't work then.

    Edit No wait a minute. It is working now. I was testing with pre-2017 dates in E1. Those won't work.
    Last edited by FlameRetired; 11-06-2017 at 10:39 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 10-06-2015, 05:30 PM
  2. [SOLVED] Date functions YEAR, MONTH, EOMONTH do not work for the array format?
    By alice2011 in forum Excel General
    Replies: 2
    Last Post: 05-06-2015, 08:59 PM
  3. Replies: 1
    Last Post: 04-22-2015, 08:08 AM
  4. Replies: 1
    Last Post: 11-23-2014, 06:02 AM
  5. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  6. [SOLVED] Count days for specific year falling within in multi-year date range
    By jslo2013 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2012, 05:58 PM
  7. Replies: 3
    Last Post: 08-14-2012, 05:14 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1