Expand my Community achievements bar.

SOLVED

label a task as AM or PM based on planned completion date/time

Avatar

Level 3

hello, wondering if someone can help me with this. have a project report that is pulling in task level data, and id like to either display the date and time (currently only shows date) or read the time, and if if its before noon add an AM tag, if after noon add a PM tag. 

 

here is the code i have so far. 

 

displayname=Current Task
listdelimiter=
listmethod=nested(tasks).lists
textmode=true
type=iterate
usewidths=true
valueexpression=IF({numberOfChildren}=0,IF({canStart},IF(ISBLANK({actualCompletionDate}),CONCAT({plannedCompletionDate},{plannedCompletionTime},": (",{name}," | ", {assignedTo}, " :",{status},")"))))
valueformat=HTML
width=200

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

OK, I'm weird. I love a good nested IF statement challenge!

I tested this, looks like one of your parens just needs to move. Instead of 2 parens after the "PM" move one back before the <12. I also think your > needs to change to <

 

valueexpression=IF({numberOfChildren}=0,IF({canStart},IF(ISBLANK({actualCompletionDate}),CONCAT({plannedCompletionDate}," ",IF(HOUR({plannedCompletionDate})<12,"AM","PM"),": (",{name}," | ", {assignedTo}, " :",{status},")"))))

View solution in original post

8 Replies

Avatar

Community Advisor

Hi there! Date fields in a report can show just date, or date + time. Go into the date column of your report > advanced options > there are field formats you can choose from. Is this what you're looking for?

Madalyn_Destafney_0-1678882869829.png

 

If this helped you, please mark correct to help others : )

Avatar

Level 3

@Madalyn_Destafney Hi! ideally yes that would work, but this is a project report with task level data, so to get the task level data i need to use text mode to pull in the information. i assume that "{plannedCompletionDate}" has date/time in it, but i don't know how to display it as date and time, just date at the moment. 

Avatar

Community Advisor

@flieckster  try replacing the valueformat in your text mode with this.

valueformat=longAtDate

 

To find this, I took a project report, added the date format that Madalyn mentioned to a date field, then flipped that to text mode too see the valueformat

Avatar

Level 3

hey @Heather_Kulbacki thanks for writing back! i tried that actually, and it works great if i'm just calling the date, but in my value expression i'm calling for other fields, so it doesn't seem to work. is there a way to have multiple value expressions so i'm just formatting the date and not any other fields?

 

 

Avatar

Community Advisor

Gah! I forgot, you're doing a concat, not a calculation returning just a date/time.

 

Here's my next attempt:

valueexpression=IF({numberOfChildren}=0,IF({canStart},IF(ISBLANK({actualCompletionDate}),CONCAT({plannedCompletionDate}," ",HOUR({plannedCompletionDate}),":",MINUTE({plannedCompletionDate}),": (",{name}," | ", {assignedTo}, " :",{status},")"))))

 

So after the date, you'll pull in the hour from the planned completion date field, followed by a colon, then the minute of the planned completion date field, then the rest as you had it.

Avatar

Level 3

wow that did it! it does pull back 24 hour time, really i'm just looking to take the info and evaluate the hour and display AM or PM. i was trying to come up with some like below where if its before noon it would display AM but after display PM, but i'm not sure i'm close on that or if its totally wrong. 

 

displayname=Current Task
listdelimiter=
listmethod=nested(tasks).lists
textmode=true
type=iterate
usewidths=true
valueexpression=IF({numberOfChildren}=0,IF({canStart},IF(ISBLANK({actualCompletionDate}),CONCAT({plannedCompletionDate}," ",IF(HOUR({plannedCompletionDate}>12,"AM","PM")),": (",{name}," | ", {assignedTo}, " :",{status},")"))))
valueformat=HTML
width=200

Avatar

Correct answer by
Community Advisor

OK, I'm weird. I love a good nested IF statement challenge!

I tested this, looks like one of your parens just needs to move. Instead of 2 parens after the "PM" move one back before the <12. I also think your > needs to change to <

 

valueexpression=IF({numberOfChildren}=0,IF({canStart},IF(ISBLANK({actualCompletionDate}),CONCAT({plannedCompletionDate}," ",IF(HOUR({plannedCompletionDate})<12,"AM","PM"),": (",{name}," | ", {assignedTo}, " :",{status},")"))))

Avatar

Level 3

amazing! boy, i was so close, but i don't think i would have figured that out lol. thank you