Solved: Changing time string?

[Simon Lloyd]

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]

[vba]

MyTime = "#" & Format(Now() - TimeSerial(0, 15, 0), "h:mm AM/PM") & "#"
[/vba]

[Simon Lloyd]

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]

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]

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]

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:
[vba]
MyTime = Time() - TimeSerial(0, 15, 0)
[/vba]

If I've gone goofy, sorry.

Mark

[Simon Lloyd]

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]

GTO, in reality that does work however i am using MyTime & MyTime1 where:
[vba]
DIM MyTime As Double, MyTime1 As Double
MyTime = #5:30:00 AM#
MyTime1 = #5:30:00 PM#
[/vba]
In this:
[vba]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[/vba]

[Bob Phillips]

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]

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]

How about this?

[vba]

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
[/vba]

[Simon Lloyd]

Hi Bob, i could just use this[VBA]eformula = "=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)[/VBA]

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]

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]

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]

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]

Ok Bob, will call after 7:15 (don't get home until then), if thats too late perhaps tomorrow maybe around lunch?

[Bob Phillips]

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]

Nextr try

[vba]

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
[/vba]

[Simon Lloyd]

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]

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.

[vba]

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
[/vba]

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