My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 result based on now()
|
|
 May 19 2004, 04:20 AM
Post
#1
|
|
|
UtterAccess VIP Posts: 13,031 From: Leicester, UK  |
Can anyone suggest what I am doing wrong....
I want to generate the time the file was opened/refreshed (no problem doing this) and then if it is before 11am I want to display the last weekday but if it's after 11am to display todays date. I have tried it but it never seems to recognise the time... I must be using the wrong format the wrong format. See example
Attached File(s)
|
 |
 
|
|
May 19 2004, 05:32 AM
Post
#2
|
|
|
UtterAccess Veteran Posts: 482 From: UK |
Danny,
Have you tried in =IF(A1<A2,NOW()-1,NOW()) in cell A3 ....remember to format CELL A3 to something like custom dd/mm/yyyy ...or it will just show you the date's numeric value. HTH Stu |
|
|
|
|
May 19 2004, 08:28 AM
Post
#3
|
|
|
UtterAccess VIP Posts: 13,031 From: Leicester, UK |
doesn't work. just returns today as the value and not yesterday when it's before 11.
|
|
|
|
|
May 19 2004, 09:13 AM
Post
#4
|
|
|
UtterAccess Veteran Posts: 482 From: UK |
Sorry it didn't work for you ...as a slightly different version, try..
Cell A1 =NOW() Format hh:ss Cell A2 =IF(A1="11:00",NOW(),"11:00") Format hh:ss Cell A3 =IF(A1<A2,NOW()-1,NOW()) Format dd/mm/yyyy This works, for me. (edit ...not stable though, having just gone back to it..if you change, say 11:00 to 22:00, then it doesn't change the result to 19/5/04 UNTIL you refresh the formula in A3) ..you could use an after update piece of code to achieve this. HTH Stu Edited by: Squire_King on 05.19.04. Edited by: Squire_King on 05.19.04. |
|
|
|
|
May 19 2004, 09:54 AM
Post
#5
|
|
|
UtterAccess Veteran Posts: 348 From: NW Iowa, USA |
Maybe one of these might help:
For cell (A3) CODE =IF(TIMEVALUE(TEXT(A1,"h:mm"))>TIMEVALUE(TEXT(A2,"h:mm")),"after 11 " & TEXT(TODAY(),"mm/dd/yy"),"before 11 " & TEXT((TODAY()-1),"mm/dd/yy")) Or when the workbook opens CODE Private Sub Workbook_Open() If Now() < Date + TimeValue("11:00") Then Range("A1") = (Date - 1) & " " & Time 'Assign your range here Else Range("A1") = Date & " " & Time 'Assign your range here End If End Sub With the Workbook_Open(), I formatted the cell as m/d/yy h:mm AM/PM Edited by: Grove on 05.19.04. |
|
|
|
|
May 19 2004, 10:29 AM
Post
#6
|
|
|
UtterAccess Veteran Posts: 482 From: UK |
|
|
|
|
|
May 19 2004, 10:32 AM
Post
#7
|
|
|
UtterAccess VIP Posts: 13,031 From: Leicester, UK |
I used your formula. worked well.
I had to take the formatting off of the results though as the date was used ina vlookup for other dates. thanks very much. |
|
|
|
|
May 19 2004, 08:29 PM
Post
#8
|
|
|
UtterAccess Veteran Posts: 348 From: NW Iowa, USA |
I'm glad it worked for you.
_______________________ Stu, Thank you. Slowly, but surely, I'm trying to learn this outfit. At least I'm starting to be able to contribute back from what I've received. |
|
|
|
|
May 20 2004, 12:24 AM
Post
#9
|
|
|
UtterAccess Veteran Posts: 482 From: UK |
Grove,
That's one of the great things about this site, being able to 'raise the level of your own game and share that so others can do the same'. Stu (IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif) ----------------- ...The lips of the wise spread knowledge; not so the hearts of fools...Proverbs 15:7 |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 22nd May 2013 - 11:11 AM |