LinkBack URL
About LinkBacks
I'm trying to get the current TIMEVALUE. So I'd like a number between 0 and 1 representing the day's time. TIMEVALUE(NOW()), to my surprise, does not work. I have searched to no avail. How can I do this? Thanks
I'm trying to get the current TIMEVALUE. So I'd like a number between 0 and 1 representing the day's time. TIMEVALUE(NOW()), to my surprise, does not work. I have searched to no avail. How can I do this? Thanks
Tryformatted as time.Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Or this
=MOD(NOW(),1)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Huh, so there's really no direct way to do it other than rounding. I just think that's weird. Thanks for the quick help to both of you.
That's not rounding. NOW() is the date plus the time. The integer part is the number of days counting from 01/01/1900 and the fractional part is the part of a day, that is, the time.
TIMEVALUE takes a string that looks like a time and converting it into a true numeric value representing the time.
INT function - "Rounds a number down to the nearest integer."
Sounds like rounding to me. Thanks for the help.
The formula is taking the integer part of the NOW() value from the total NOW() value.
Rounding would be reducing the number of decimal places in the output value, whereas this is leaving you with only the decimal places.
So, OK, the first part is rounding but, overall, it's not. Is rounding a problem for you?
TIMEVALUE doesn't give you what you want
Anyway, thanks for the rep
Well obviously I didn't mean rounding the output, that wouldn't get me what I want! I thought of that when I was trying to figure out a way, but thought I might be missing something with the date formatting. (Google Sheets, which I was just using, returns what I'm looking for with TIMEVALUE(NOW()) )
OK,I'm done. I'll leave it with you.
Just tested:
=TIMEVALUE(NOW()) in Google sheets produces the same result as =MOD(NOW(),1)
If you want to to use =TIMEVALUE(NOW()) in excel use it like the formula below
=TIMEVALUE(TEXT(NOW(),"m/d/yyyy h:mm:ss"))
In Excel the TIMEVALUE function doesn't work on numbers which is what the NOW() function returns.
NOW() returns the date and time which is a numeric value.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
The date portion is not needed:Originally Posted by AlKey
=TIMEVALUE(TEXT(NOW(),"h:mm:ss"))
And, the TIMEVALUE function is not needed:
=--TEXT(NOW(),"h:mm:ss")
Thanks AlKey and Tony, that's closer to what I was originally looking for and it explains why I couldn't get it to work. They all work the same, though, thanks!
You're welcome. We appreciate the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks