• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

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.
0
Stephen Byrom
Asked:
Stephen Byrom
  • 5
  • 3
1 Solution
 
andrew_manCommented:
=IF(NOW()-TODAY()<TIME(8,5,0),-1,0)
0
 
Stephen ByromAuthor Commented:
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
0
 
andrew_manCommented:
Can you attach your file here?  I will help to fix.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Stephen ByromAuthor Commented:
Ok.
This is still in it's embryo stage
Production.xlsm
0
 
andrew_manCommented:
Okay! I dun know!

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

Thanks!
Production.xlsm
0
 
andrew_manCommented:
Okay! I dun know you dun know the basic function.  Sorry!  

Keep in touch!

Andrew
0
 
Stephen ByromAuthor Commented:
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 :)
0
 
andrew_manCommented:
Good!  I can help you!

Keep in touch!

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now