+ Reply to Thread
Results 1 to 14 of 14

NOW function woes.

  1. #1
    Registered User
    Join Date
    09-29-2014
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    30

    NOW function woes.

    Hi all.

    Im working on a timesheet and have been using the keyboard shortcut "control, shift,colon" to enter a time stamp in a start time and finish time cell and a final cell to calculate total time.

    I decided to use the NOW function to insert a time stamp automatically in the start cell but when I use this formula the calculation for total time returns a result of ########################

    The NOW function formula looks like this. =IF(F5<>"",IF(H5="",NOW(),H5),"")

    and the total time formula looks like this =IF($J5="YES",(($I5-$H5)-(60/1440)),IF($J5="",($I5-$H5),""))


    Im very much a beginner with excel and both of the formulas have been copied from tutorials so if there is a better formula to use please suggest it.

    Otherwise, how can i correct this?

    Thank you.
    Last edited by kassysimon; 11-30-2014 at 09:23 PM.

  2. #2
    Registered User
    Join Date
    12-15-2011
    Location
    Washington
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: NOW function woes.

    Make sure the cell you put the formula in is formatted as the "Date" type.

  3. #3
    Registered User
    Join Date
    09-29-2014
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    30

    Re: NOW function woes.

    Thanks but im using it as a time stamp to enter the time rather than a date and the cell is formatted as Custom h:mm.

    Can it be used this way or is the above formula only for dated timestamps?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,369

    Re: NOW function woes.

    Hi, welcome to the forum

    Working with time in excel can be a royal pain sometimes. For a start, excel does not like negative time (not saying that is the problem here, just saying)

    Remember that the NOW() function includes the date AND time, so you could be testing with disparate values here.

    You need to understand this about dates...a date is just a number indicating how many days have passed since 1/1/1900, so 7/11/14 is actually 41831. excel formats it into something that we recognise as a date

    Likewise with time...time is a decimal of 1 (day), so for instance 06:00 AM is actualy just 0.25, 12 noon is .05 and 06:00 PM (18:00) is 0.75. Again, excel formats it to something we see as a time

    So NOW() for 11/30/2014 20:07 is actually 41973.83843
    Hopfully that will help get you back on track

    If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    09-29-2014
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    30

    Re: NOW function woes.

    Thanks for the heads up.

    Its seems that this might be the wrong formula for me to use as i require separate cells for date and time.

    Thanks all the same.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,369

    Re: NOW function woes.

    Quote Originally Posted by kassysimon View Post
    Thanks for the heads up.

    Its seems that this might be the wrong formula for me to use as i require separate cells for date and time.

    Thanks all the same.
    This can still be done using MOD() and INT(), but if you provide a sample workbook, I can take a look for you

  7. #7
    Registered User
    Join Date
    09-29-2014
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    30

    Re: NOW function woes.

    Thanks FDibbins.

    Heres a workbook.

    Thanks for your help.
    Attached Files Attached Files

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,369

    Re: NOW function woes.

    OK 1 thing about NOW() is that it is a volatile function, it does just what it says...it shows the time right now. It will update every time there is any change to the workbook, and show the latest time. In other words, it will keep changing, so it cant really be used as a time stamp, the way (I think) you want)

    As a test, enter =NOW() into a cell and take note of the time, then wait a few minutes and press F5 (Calc). You will see that the time changes from what it showed previously.

    also, where you have that formula in H5...
    =IF(F5<>"",IF(H5="",NOW(),H5),"")
    ...this is causing a circular error because you are referencing H5 FROM H5

  9. #9
    Registered User
    Join Date
    09-29-2014
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    30

    Re: NOW function woes.

    Thanks mate.

    I dont think NOW is the right way to go either.

    cheers

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,369

    Re: NOW function woes.

    Time is relatively easy/simple to enter into a cell, just type in 08: for 8 AM (or any on-the-hour) or 8:14
    For past midday, use 15: or 15:12 or 3: PM or 15:12 PM

    I was also just looking at that monster you have in column G. I will see if I can simplify that a bit for you

  11. #11
    Registered User
    Join Date
    09-29-2014
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    30

    Re: NOW function woes.

    Thank you very much.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,369

    Re: NOW function woes.

    OK I have had to add 2 helper columns to your AD and AH sheets, inserting them after C. This creates a unique identifier for each row. Then I used that in a single INDEX/MATCH to bring in the answer.
    What I cant figure out is when to decide which sheet to look in for the answer lol, but maybe you can adjust the formula to do that for you (you know what you are doing, I dont)

    Anyway, take a look at the 2 yellow cells in Task Management and see if that is something you can work with
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    09-29-2014
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    30

    Re: NOW function woes.

    Quote Originally Posted by FDibbins View Post
    OK I have had to add 2 helper columns to your AD and AH sheets, inserting them after C. This creates a unique identifier for each row. Then I used that in a single INDEX/MATCH to bring in the answer.
    What I cant figure out is when to decide which sheet to look in for the answer lol, but maybe you can adjust the formula to do that for you (you know what you are doing, I dont)

    Anyway, take a look at the 2 yellow cells in Task Management and see if that is something you can work with
    WOW, Its been FIVE years since I lasted visited this thread and I cant believe that I didnt notice that FDibbins provided me with a solution to my issues, I just never realised that you responded.

    So a BIG THANK YOU for your help and I apologies for not responding.

    Thanks again.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,369

    Re: NOW function woes.

    wow, you can say that again!! Thanks so much for returning and commenting on the assistance provided, always appreciated - even more so after such a long time

+ 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