Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using rounded time with vlookup
I'm trying to use the =now() function to provide the current time, and use it
to reference another sheet containing a schedule so it will auto-populate with the information depending on the time of day, and the day of the week. I have the day of the week figured out, but getting time to round to the nearest hour (and exclude the date) is pretty rough. I have been successful at rounding to the nearest hour, but no matter what I do, it includes the date and as such will not refer to a schedule containing times like 1:00 pm as the cells that I am using contain (4-29-06) 1:00pm... The date is hidden due to the format of the cell, yet the number is still a part of the information there. Here is the series I have so far. =now() =FLOOR(G1,"01:00") Is there any way for me to get rid of the date, or should I scrap the whole thing :) Sorry if that was unclear, this has given me a headache ;) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using rounded time with vlookup
Try this instead of just NOW():
=MOD(NOW(),1) Hope this helps. Pete |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using rounded time with vlookup
To get the hour of the current time (in time format) with just one formula =HOUR(NOW())/24 format as time -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=537526 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using rounded time with vlookup
Thanks guys! Both worked like a charm! :)
"daddylonglegs" wrote: To get the hour of the current time (in time format) with just one formula =HOUR(NOW())/24 format as time -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=537526 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hot key for time? | New Users to Excel | |||
Hot key for time? | Excel Worksheet Functions | |||
Hot key for time? | Excel Discussion (Misc queries) | |||
time sheet to calculate 2 different columns | Excel Worksheet Functions | |||
Time and motion chart | Charts and Charting in Excel |