Deriving a live TIMEVALUE from the NOW() function

[Rob_GC_Excel]

Hi,

I'm currently building a data model for my work, I've built a giant =IFS statement to return a value based on the current time:

=IFS(AND(F3>=TIMEVALUE("00:00 AM"),F3<TIMEVALUE("00:15 AM")),"00:00 - 00:15",AND(F3>=TIMEVALUE("12:15 AM"),F3<TIMEVALUE("12:30 AM")),"00:15 - 00:30" ... "23:45 - 00:00"

(This code returns a string value for a 15-minute time range. The time string "HH:MM - HH:MM" is the unique value in column A that connects to the amount of Gross Sales in that time in column B. That's not really important, just a bit of background information on what I'm trying to achieve.)

The value F3 is where the current time is stored. Originally I used the NOW() function and formatted it to hours and minutes, but that was returning an #N/A value. When I manually enter a time, the formula works fine, returns the correct value. You just can't get a timevalue on the NOW() function.

Ideally I wanted to have the spreadsheet I'm working on automated so that whenever I refresh it updates the time automatically, if I can't than it's not a huge hassle, but I was wondering if anybody knew any smart workarounds taking the =NOW() formula and converting it to a hard-coded value in another cell which I can then call upon to use the IFS statement. I've tried LEFT and RIGHT functions but they return a serial number.

Thanks.

 

[sergioMabres]

H Rob
I think one way to do this is to use in the cell
=NOW()-INT(NOW())
You will get a decimal number because times in excel are decimal numbers and dates are whole numbers
So now you format the cell as time and you are all set
Cheers
Sergio

 

[Rob_GC_Excel]

Thank you, that worked perfectly.

Cheers,
Rob