This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Hi everyone - I'm hoping someone can help me with a formula.

I want to group Time Entries by dates - Group 1 = dates 1-15, Group 2 = 16-31, but I'm not sure how I would isolate those records in a Case statement - any assistance is greatly appreciated.

CASE WHEN TO_CHAR({datefield},'DD')&lt;=15 THEN '1-15' ELSE '16-31' END

replace datefield with your specific field.

Ty <@UAPHBR6TS> - the formula I currently have (which is not working) is:

Case when {type} = 'Planned Time' AND TO_CHAR({datefield},'DD')&gt;=16 THEN {durationdecimal} end

Ultimately, I want to group &amp; sum by Customer the Planned Time from dates 16-13, while tracking actual time for dates 1-15.

Any advice you can offer on correcting the formula, I'm open to your input.

Do you want {durationdecimal} in separate columns for each grouped customer based on if it's 16-31 (Planned) or 1-15 (Actual)?

Also, what's 'not working' about your formula?

<@UAPHBR6TS> Yes - ideally i am grouping Actual time from 1-15 in a single column &amp; Planned Time from 16-31 in another column

Ok are you using {trandate} in your actual formula?

you have {datefield}, which I was just using as a reference (it's not an actual field)

<@UAPHBR6TS> :sweat_smile: thats embarrassing! I updated the formula and it is working as expected now

Glad it worked out! Should be able to just duplicate/update that case statement to get that actual time column, then you're good to go!

<@UAPHBR6TS> do you think its possible to modify the criteria to be dynamic - *column 1* [actual time entries this month through {today}], *column 2* [allocated time entries {tomorrow} through end of month]?

Wouldn't see why not. Take a look at some of the formulas on this page <http://www.melioza.com/2015/09/netsuite-report-dates.html>

Specifically, "Month To Date" for your column 1 and "Last Day of This Month" for your column 2. I might have some time later today to see about specific formulas for your needs.

I've used the formulas on that site quite a lot. I think the "Month To Date" one would only require you to modify what is spits out if it's true.

You'll have to combine the Last Day of This Month with other criteria...probably like CASE WHEN {trandate} BETWEEN {today}+1 AND LAST_DAY(TRUNC({today}, 'MONTH'))

<@UAPHBR6TS> works as expected - thank you again!