IF on a date field

[Paul_Christie]

Please can anyone help me with the syntax of an if on a date field bearing in mind the difference between UK and US dates. I have data for 365 days, however on two of those days instead of 24 hours I have one of 23 and one of 25. If I wanted to sum everything it wouldn't be an issue but I only want to sum certain hours and on the two days i need to shift the hours. I can do this in Excel by adding a column to the query table but because I'm querying 300,000 lines of data from another source it seems the right thing to do it in the Query.

 

[Paul_Christie]

I get some satisfaction out of answering my own questions and I'm not just doing this to get my post count up. The syntax for what I'm trying to do is
if [Date] = #date(2015, 10, 25) Then
[SP 35]+[SP 36]+[SP 37]+[SP 38]+[SP 39]+[SP 40]
else
if [Date] = #date(2016, 3, 27) then
[SP 31]+[SP 32]+[SP 33]+[SP 34]+[SP 35]+[SP 36]
else
[SP 33]+[SP 34]+[SP 35]+[SP 36]+[SP 37]+[SP 38]

All i need to do now is parameterize the two dates to make it a bit more end user friendly. In case anyone is wondering this is to do with annual energy consumption that needs to take into account the two clock change days.

 

[Paul_Christie]

Ken,

I've used Chapter 23 in your book to create a GetParameter function but I can only get it to work on a text field. Would it work on a date field if I got the syntax correct?

Paul

 

[Ken Puls]

I've used Chapter 23 in your book to create a GetParameter function but I can only get it to work on a text field. Would it work on a date field if I got the syntax correct?l

Absolutely. Check pages 219-220 for the conversion functions you need. To convert text to a date, you'd want:
=Date.ToText(fnGetParameter(paramname),"yyyy-mm-dd")

And to go the other way:
=Date.From(fnGetParameter(paramname))

 

[Paul_Christie]

Ken,

Thanks for that last tip. I've got it working perfectly.

Paul