Is there a way to not type DATE formula manually?

I have a sheet with the date as the column name and have a cross-sheet formula with SUMIFS and DATE. Is there a way I don't have to type the date manually? Unfortunately, I have over 100 columns that I need to modify the date to match the column 😥

You'll be a lifesaver if there is a way!

Thank you in advance!!🙇‍♀️

Find more posts tagged with

Formulas

Accepted answers

Andrée Starå

Hi @Christina Lam

I hope you're well and safe!

To add to Gia's excellent advice/answer.

Another option could be to export the sheet to Excel and copy and paste the row with the dates.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

All comments

Gia Thinh

Hi @Christina Lam,

Yes, it's a big problem for you if the number of date columns increasing.

How do you think about re-contruction your sheet? I mean we can convert date columns to rows and rows to columns. It will save your time.

Christina09

Hi @Gia Thinh

I have a rollup sheet with parent and child rows. Don't think it'll work since I needed to compare the data by date for each row.

Thanks.

Gia Thinh

Hi @Christina Lam,

I got an idea that we add one row of string-formated date in the first row as below screenshot with formula to calculate date in the next cell.

Then we can you date-cell in the first row to do the comparison in the cell of next rows.

This is DATE formula to calculate date from the date-cell in the first row

DATE(VALUE(RIGHT([09/10/21]$1; 4)); VALUE(LEFT([09/10/21]$1; 2)); VALUE(MID([09/10/21]$1; 4; 2)))

Hope that helps.

Christina09

@Gia Thinh

This is a great idea, but it wouldn't work for next month and the month after and so on.

Andrée Starå

Hi @Christina Lam

I hope you're well and safe!

To add to Gia's excellent advice/answer.

Another option could be to export the sheet to Excel and copy and paste the row with the dates.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Christina09

@Andrée Starå

Thank you, I don't know why I never thought of that. That is a great way, works like a charm. 👍️😀

Source sheet:

I've also modified my linking sheet by adding a text type of date column. So it would pull the information in the other sheet.

My question is: I have W/E column as Date column, and Texting weekending as a helper column in text/number. Currently, I have a column formula to convert the W/E Date to text:

=LEFT([W/E]@row, 2) + "/" + VALUE(MID([W/E]@row, 4, 2)) + "/" + RIGHT([W/E]@row, 2)

The problem is that if the day is a single digit, it wouldn't convert it to MM/DD/YY like W/E column. see highlight below for reference.

Could you please guide me?

Thanks.

Christina09

Solved!! a simple formula like this will work 🤣 =[W/E]@row + ""

Andrée Starå

@Christina Lam

Excellent!

Glad you got it working!

I'm always happy to help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.