# NOW function woes.

• 11-30-2014, 08:58 PM
kassysimon
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.
• 11-30-2014, 09:02 PM
dingdongsilver
Re: NOW function woes.
Make sure the cell you put the formula in is formatted as the "Date" type.
• 11-30-2014, 09:04 PM
kassysimon
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?
• 11-30-2014, 09:08 PM
FDibbins
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.
• 11-30-2014, 09:10 PM
kassysimon
Re: NOW function woes.

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.
• 11-30-2014, 09:13 PM
FDibbins
Re: NOW function woes.
Quote:

Originally Posted by kassysimon

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
• 11-30-2014, 09:24 PM
kassysimon
Re: NOW function woes.
Thanks FDibbins.

Heres a workbook.

• 11-30-2014, 09:41 PM
FDibbins
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
• 11-30-2014, 09:43 PM
kassysimon
Re: NOW function woes.
Thanks mate.

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

cheers
• 11-30-2014, 09:55 PM
FDibbins
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-30-2014, 09:55 PM
kassysimon
Re: NOW function woes.
Thank you very much.
• 12-01-2014, 03:23 AM
FDibbins
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
• 01-24-2019, 09:17 PM
kassysimon
Re: NOW function woes.
Quote:

Originally Posted by FDibbins
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.
• 01-25-2019, 05:14 AM
FDibbins
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 :)