InfoPath Dev

Hey everyone,

I would like to start off with a thank you to everyone because I've been trolling these boards for awhile and they are of great help.

Let me set up what I am doing for you...

I am trying to return specific results from a list of tickets(taken from a SharePoint List) by Last Week(past seven days), this month(xxx/01/xxxx) and Year to date(Jan/01/xxxx).

I have had success with the "Last Week". I created a field(LastWeekReference) and entered this function "addDays(today(), -6)"

Then to return the number of tickets titled "SPOC Tickets" since last week. I used "count(@Origin[(@Origin = "SPOC Ticket") and (msxsl:string-compare(@DateCreated, LastWeekReference) > 0)])" and it works great.

What would the format for This Month's reference column be? I do not want to say "addDays(today(),-30)" because eventually the count will be off.

 Is there something like the excel function "&DATE(YEAR(TODAY()),MONTH(TODAY()),1)"? <--- (in excel this returns the xxx/01/xxxx for whatever month you access it in.)

Thanks!

-M

This formula should work:

concat(substring(today(), 1, 8), '01')

Exactly what I needed. Thanks!

Now if you didn't mind... 

What would need to be modified in that to give "Jan/01/xxxx"?

Figured it out...

I can just return the current year with 

concat(substring(today(), 1, 4), "")

Then when i reference this field, i just apply the filter contains "Reference cell"

and it only returns values from this year

now one more question...

How would return this with a time of 9:30 AM?!

Currently I am pulling data from last week(tues 9/28- mon 9/4). BUT I really want to be pulling it starting at "9/27 at 9:30 AM" because this is the time I do it everyday.

Without adjusting this I lose all the data with a created date of that monday between the hours of 9:30 AM -11:59 PM.

Thanks!

concat(addDays(today(), -7), 'T09:30:00')

should do the trick.