Discuss all things Remember The Milk.

In case you forgot: You're awesome! (batch-add daily affirmations with a spreadsheet and email)

megan.strickland says:
I struggle with self-criticism and lack of confidence, particularly when I am staring at my to-do list (however nicely organized it is---thank you RTM!). Therefore, it is helpful to me to leave myself encouraging daily notes within my to-do list, because that is where I need it most. Plus, feeling better helps me be more productive. Yay!

Inspired by:
rmcmullan - Building Conveyor Belts

Here's the method:

1) Set up a spreadsheet. I used Google Drive, but Excel would probably work as well if you can find the equivalent functions.

2) Column A is your affirmations. Put in whatever you want. I decided to do 4 weeks worth (28).

3) Column B is tags. Put in whatever tags you want applied, using the quick add syntax. You can use different tags for different affirmations, or easily put it in once and copy/paste down the whole column. (I use a sleeper tag #zzz to keep them from showing up before their day, and a #♥ tag to identify all my affirmations.)

4) Column C is the export column. It uses a formula to put it all together and add due dates. Example, for row 2:

=CONCATENATE(A2," ^",(ROW()-1)," days ",B2)

Put this in once and then just copy/paste down the whole column.

This results in something like "You are loved. ^1 days #zzz #♥"

5) Column D is the character limit check. Many people (including me) encounter problems adding very long tasks via email. To help identify any strings that are too long, use the following (again, example is for row 2):


This shows the string length of your "export" column. I use conditional formatting to highlight anything over 72 characters, as this seems to be my cutoff. I don't know of a good work around, so I just shorten anything that is too long.

6) Add one more item at the end of the list:

Column A: "Reload affirmations"
Column B: whatever tags you want
Column C: =CONCATENATE(A20," ^",(ROW()-2)," days ",B20)

Note the change from "ROW()-1" to "ROW()-2". This makes your "reload affirmations" tasks show up on the same day as your last affirmation, so you don't have a gap.

7) Select and copy all of your tasks from Column C only. Paste into an email. If the cell grid copies through, select all and clear formatting. Send to your RTM import address.

Posted at 12:44am on January 22, 2014
emily (Remember The Milk) says:
Hi Megan,

This is a nice (and creative!) use of the email import feature.

Thanks for sharing your tip -- you're this week's Tips & Tricks Tuesday winner. We've added a free year of Pro to your account. :)
Posted 2 years ago
megan.strickland says:
Thank you!
Posted 2 years ago
lilyheart says:
If you want to force column C to never be more then 72 characters and to just cut off anything longer then that you can use this formula. You can change the 72 to whatever length you want.

=CONCATENATE(LEFT(A2,(72-LEN(B2&" ^"&(ROW()-1)&" days ")))," ^",(ROW()-1)," days ",B2)

The formula figures out how long everything else being added to the field is and then takes only 72 characters minus that of the affirmation.

An alternative way is to use this formula. It will put ~~TOO LONG~~ instead of the affirmation in that field and you could conditional formatting that.

=IF(LEN(CONCATENATE(A2," ^",(ROW()-2)," days ",B2))<=72,CONCATENATE(A2," ^",(ROW()-2)," days ",B2),"~~TOO LONG~~")

If these work better but still don't work quite right let me know why and I can fix it.
Posted 2 years ago
Log in to post a reply.