Avatar of Stephen Byrom
Stephen ByromFlag for Ireland asked on

EXCEL time/date

Hi,
Cell A1 shows the date and time using Now().
I wish a different cell to show that date minus 1 if the time is less than 08:05.
I have tried many permutations of datevalue and timevalue but i'm slowly losing the will to live.
Any help is greatly appreciated.
Microsoft Excel

Avatar of undefined
Last Comment
andrew_man

8/22/2022 - Mon
andrew_man

=IF(NOW()-TODAY()<TIME(8,5,0),-1,0)
ASKER
Stephen Byrom

Thanks for the speedy response Andrew.
If I place that formula in a cell it just shows lots of hash marks, no matter how large I make the cell or whatever format I change it to.
Is there a way to use the IF statement in the target cell?
Such as
IF cell A1 is less than time 08:05 then show yesterday's date and time, else show today's date and time

Thanks again
andrew_man

Can you attach your file here?  I will help to fix.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Stephen Byrom

Ok.
This is still in it's embryo stage
Production.xlsm
ASKER CERTIFIED SOLUTION
andrew_man

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
andrew_man

Okay! I dun know you dun know the basic function.  Sorry!  

Keep in touch!

Andrew
ASKER
Stephen Byrom

Thanks for your time Andrew (no pun intended)
As you can probably guess, I will be asking a few more questions on here before I finish that workbook :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
andrew_man

Good!  I can help you!

Keep in touch!

Andrew Man from Hong Kong
andrew.man@live.cn