PDA

View Full Version : Solved: Changing time string?



Simon Lloyd
06-14-2010, 09:26 AM
Hi all quick question thats been puzzling me, how do you change
MyTime = #5:30 AM# to
MyTime = # Now()-15 AM#

I'm looking to have the time set in the first MyTime format but want the time to be 15 minutes less than now?

Bob Phillips
06-14-2010, 10:49 AM
MyTime = "#" & Format(Now() - TimeSerial(0, 15, 0), "h:mm AM/PM") & "#"

Simon Lloyd
06-14-2010, 11:51 AM
Thanks Bob, i did try using & format(now....etc but couldn't fathom using timeserial OR the h:mm am/pm.

Thanks a lot my friend :)

Simon Lloyd
06-15-2010, 10:35 AM
Sorry Bob, i had to mark this thread unsolved as your solution gave a Runtime error 13, so for now i have reverted to #5:30:00 AM# etc

Any ideas?

Bob Phillips
06-15-2010, 10:42 AM
Really, in what context? I did

?"#" & Format(Now() - TimeSerial(0, 15, 0), "h:mm AM/PM") & "#"

in the immediate window, and this worked fine.

GTO
06-15-2010, 01:23 PM
I'm looking to have the time set in the first MyTime format but want the time to be 15 minutes less than now?

Howdy to both ya'll,

I think maybe just a mis-communication due to the inclusion of the 'AM' in 'MyTime = # Now()-15 AM#'

Simon,

If you are trying to return a Date variable, 15 minutes prior to NOW(), maybe:

MyTime = Time() - TimeSerial(0, 15, 0)


If I've gone goofy, sorry.

Mark

Simon Lloyd
06-15-2010, 11:11 PM
Bob, it may be because the original #5:30:00# is DIM'd as double, i'll switch to string and see if that helps!

GTO, i'll also give that a go.

Thanks!

Simon Lloyd
06-15-2010, 11:53 PM
GTO, in reality that does work however i am using MyTime & MyTime1 where:

DIM MyTime As Double, MyTime1 As Double
MyTime = #5:30:00 AM#
MyTime1 = #5:30:00 PM#

In this:
eformula = "=IF(AND(MOD('Lookup List'!R1,1)>TIMEVALUE(""07:00"")" & _
",MOD('Lookup List'!R1,1)<TIMEVALUE(""19:00"")),""Dust Days"",""Dust Nights"")"
If Evaluate(eformula) = "Nights" Then
Sheets("Lookup List").Range("S1").Value = _
CDate(Date - 1) _
+ IIf(Evaluate(eformula) = "Dust Nights", MyTime, MyTime1)
Else
Sheets("Lookup List").Range("S1").Value = _
CDate(Date) _
+ IIf(Evaluate(eformula) = "Dust Days", MyTime1, MyTime)
End If

Bob Phillips
06-16-2010, 12:26 AM
What are you trying to do here Simon? Your formula returns Dust Days/Dust Nights, but you check for just Nights, then ou check it again after first checking it.

I am confused!

Simon Lloyd
06-16-2010, 01:00 AM
It's been a work in progress and added to many times Bob, it should read "Dust Nights"

Basically if the formula evaluates to Dust Nights i need Sheets("Lookup List").Range("S1").Value = date & MyTime - 15 minutes else (because it would evaluate to dust days) it should be date & MyTime1 - 15 minutes.

I've been working back and fore between quite a lot of code and confused myself Bob :)

Bob Phillips
06-16-2010, 02:15 AM
How about this?



Dim eFormula, MyTime, MyTime1
Dim eFormulaResult As String

eFormula = "=IF(AND(MOD('Lookup List'!R1,1)>TIMEVALUE(""07:00"")" & _
",MOD('Lookup List'!R1,1)<TIMEVALUE(""19:00"")),""Days"",""Nights"")"

eFormulaResult = Evaluate(eFormula)
With Sheets("Lookup List").Range("S1")

.Value = IIf(eFormulaResult = "Nights", Date - 1 + MyTime, Date + MyTime1)
.NumberFormat = "dd mmm yyyy h:mm AM/PM"
End With

Simon Lloyd
06-16-2010, 02:17 AM
Hi Bob, i could just use thiseformula = "=IF(AND(MOD('Lookup List'!R1,1)>TIMEVALUE(""07:00"")" & _
",MOD('Lookup List'!R1,1)<TIMEVALUE(""19:00"")),""Dust Days"",""Dust Nights"")"
Sheets("Lookup List").Range("S1").Value = _
CDate(Date - 1) _
+ IIf(Evaluate(eformula) = "Dust Nights", MyTime, MyTime1)

The #5:30:00 ?# just isn't working because the users have started to perform the task earlier so we recieve no tag data for a future time (5:30)

The reson i need to distinguish the different times is because we extract data using the PI tags and we need to give the tags a server time (with the -15 minutes because of reporting latency) to produce the figures, if a user performs the task prior 7am then its the night shift (and i'm trying to work on this bit) and the time should NOT exceed 6am (change over of recorded figure) if the task is performed after 7am then the time should not preceed 6am nor exceed 6pm.

If the task is performed after 7pm the time should not preceed 6pm nor exceed 6am, thats all probably clear as mud, our data recording is 6am - 6pm and 6pm - 6am but the user may perform the task up to 7 am or pm.

Bob Phillips
06-16-2010, 02:30 AM
As far as I can see, that is the same as mine, except you aren't formatting as I do.

You don't need to cast Date, a date is a date, and it is a tad OTT to create Duts Days and Dust Nights, Days and Nights is just as meaningful, and a lesser test.

But that apart, is it working now or not?

Simon Lloyd
06-16-2010, 02:47 AM
Hi8 Bob, i have to create Dust Days/Nights for a specific reason, anyway that aside i tried your version, however, i cannot use DATE - 1 as the date needs to be the current date and cannot use #x:xx:xx AM/PM# as a fixed time no longer suits but do need to differentiate between times as poorly explained in my last post.

This is all because our night shifts span 2 dates but for the purposes of the PI tags the times are relevant.

Bob Phillips
06-16-2010, 03:21 AM
I am going to have to read your last post again, because I don't understand :(

Why don't you call me later today?

Simon Lloyd
06-16-2010, 03:35 AM
Ok Bob, will call after 7:15 (don't get home until then), if thats too late perhaps tomorrow maybe around lunch?

Bob Phillips
06-16-2010, 05:10 AM
Ok Bob, will call after 7:15 (don't get home until then), if thats too late perhaps tomorrow maybe around lunch?

No, that is fine then.

Bob Phillips
06-16-2010, 01:56 PM
Nextr try



Dim eFormula, MyTime, MyTime1
Dim eFormulaResult As String

eFormula = "IF(OR(AND(MOD(R1,1)>=--""06:15"",MOD(R1,1)<=--""07:00""))," & _
"INT(R1)--""06:00"",R1-""00:15"")-" & _
"(NOT(OR(MOD(R1,1)>=--""17:00"",MOD(R1,1)<--""07:00"")))"

With Worksheets("Lookup List").Range("S1")

.Value = Evaluate(eFormula)
.NumberFormat = "dd mmm yyyy hh:mm"
End With

Simon Lloyd
06-17-2010, 02:10 AM
Thanks for the great reply Bob, however that formula will produce a time beyond 6pm if the time is past 6:15pm however the AM works fine right up to 06:59.

P.S I don't for one minute pretend to understand whats going on in that formula :)

Bob Phillips
06-17-2010, 03:32 AM
We are inching forward. I had recognised that I had o deal with that period, even had the AND in the formula, but forgot to extend it

Next effort.



Dim eFormula, MyTime, MyTime1
Dim eFormulaResult As String

eFormula = "IF(OR(AND(MOD(R1,1)>=--""06:15"",MOD(R1,1)<=--""07:00""),AND(MOD(R1,1)>=--""18:15"",MOD(R1,1)<=--""19:00""))," & _
"INT(R1)+ROUNDDOWN(MOD(R1,1)*4,0)/4,R1-""00:15"")-" & _
"(NOT(OR(MOD(R1,1)>=--""17:00"",MOD(R1,1)<--""07:00"")))"

With Worksheets("Lookup List").Range("S1")

.Value = Evaluate(eFormula)
.NumberFormat = "dd mmm yyyy hh:mm"
End With


BTW, why did hyou go the Evaluate method rather than just pure VBA?

Simon Lloyd
06-17-2010, 04:07 AM
Bob that's perfect when the sheet in question is the activesheet, however this sheet is never available to the user, i adjusted it to this but it fails, could you tell me what i have missed?

eFormula = "IF(OR(AND(MOD('Lookup List'!R1,1)>=--""06:15"",MOD('Lookup List'!R1,1)<=--""07:00""),AND(MOD('Lookup List'!R1,1)>=--""18:15"",MOD('Lookup List'!R1,1)<=--""19:00""))," & _
"INT('Lookup List'!R1)+ROUNDDOWN(MOD('Lookup List'!R1,1)*4,0)/4,'Lookup List'!R1-""00:15"")-" & _
"(NOT(OR(MOD('Lookup List'!R1,1)>=--""17:00"",MOD('Lookup List'!R1,1)<--""07:00"")))"

As i said earlier this has been a development progression that has seen much redundant code...etc, however, i chose to evaluate as that eformula is used and referenced elsewhere in the code for days and nights

Bob Phillips
06-17-2010, 04:54 AM
My guess Simon is that the addition of the sheet name makes the string to evaluate too long, you have added another 112 characters.

Simon Lloyd
06-17-2010, 04:54 PM
Hi Bob, just been messing around with the sheets, i have now changed the forumla to use Now() instead of 'Lookup List'!R1, R1 was just a time and date generated from elsewhere at the time of the task being performed, so Now() sufficed and shaved a few bytes of the workbook :)

Thanks for all your help, i must get you to explain the coercion and MOD if and when i get to attend any future conferences :thumb

P.S it must have been the evaluation length, i did have a look round but couldn't find something concrete on the limitation unles it follows the 255 character rule, the evaluation length was 341

Bob Phillips
06-18-2010, 01:01 AM
My thought was to create a function that returned the result, where the formula was evaluated in 3 parts and then compared in VBA. Not sure whether that would still work for you as you use it elsewhere.

Simon Lloyd
06-18-2010, 01:34 AM
The workaround using Now() has made me smarten more of the code up so i'm happy with that, i did think of putting it in a function but i felt it was somewhat a sledgehammer approach from my perspective as i really should be looking to reduce my code and not be so lazy! :)

Bob Phillips
06-18-2010, 01:49 AM
Putting code in functions is not lazy, abstractiing function ality is a god practice, especially where that code is obtuse far better not to clog up a main routine, just issue a call gto the side to do that 'nasty' work quietly and return a nice tidy result.

But I fail; to see how Now works, I thought you were testing user entered values?

Simon Lloyd
06-18-2010, 11:14 AM
Not too good at explaining myself Bob:
R1 is originally a concatenation of Shift, Date & Time of an action performed, whilst developing i removed the shift to leave a date & time.

Because of this thread it forced me to read over all the pages of code and follow the flow, it became apparent that i was always checking R1 and R1 was always NOW at the time of running the rest of the code which allowed me to clean up and get rid of superfluous code.

As you knidly supplied a fix for my dilemma i have also now tidied up further and no longer use the original eFormula for insertion elsewhere but copy it from the location it already resides in then make the original cell = original.value, keeping the amoubnt of volatile formula to a minimum.

You've been a great help.............see? :)