Stephen Byrom
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.
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.
=IF(NOW()-TODAY()<TIME(8,5 ,0),-1,0)
ASKER
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
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
Can you attach your file here? I will help to fix.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Okay! I dun know you dun know the basic function. Sorry!
Keep in touch!
Andrew
Keep in touch!
Andrew
ASKER
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 :)
As you can probably guess, I will be asking a few more questions on here before I finish that workbook :)
Good! I can help you!
Keep in touch!
Andrew Man from Hong Kong
andrew.man@live.cn
Keep in touch!
Andrew Man from Hong Kong
andrew.man@live.cn