Best Of
Re: WEEK # in Month Calculation
All we need is an IF to say that if the weekday is a Sunday (1) then add one more to the final calculation.
=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1 + IF(WEEKDAY(Date@row) = 1, 1, 0)
Paul Newcome
Re: Exactly Duplicating an existing SHEET to a new sheet
Open the sheet you would like to duplicate. Right click on the tab and select save as new. Change the name of the "new" sheet and select the location to save it. Then open the new sheet, remove the data that you don't want and re-save. You will have an exact replica.
Nicole Tellez
Re: SUMIFS with two condition in the same column
Hi @Eyglo
It looks like your column references are off a little - you'll need to wrap [square brackets] around the column name each time, instead of around the column name listed twice.
=SUMIFS([Estimated DC kW]:[Estimated DC kW], [Stage #]:[Stage #], OR(@cell = "0. Pipeline", @cell = "1. Backlog"))
Additionally I noticed your quotes are curved. Ensure they're straight up and down "" like so!
Cheers,
Genevieve
Genevieve P.
Re: New and improved Safe Sharing experience, now generally available!
@Zac Wolfram - Do you have Data Mesh or Bridge? I haven't had a chance to try it for this purpose but I am thinking in the same vein: Once a domain or external email is approved, add the domain or email address to a new row (and just add value to the one column, instead of using a Copy Row automation). Data Mesh would have to run from a report, and would be limited to hourly. Bridge could trigger immediately. @Lekshmi Unnithan - Curious if there are any restrictions to using these tools to write data to the approved emails or approved domains sheets?
Scott Peters
Re: "submit and open new form" option
Hi @drkailey
You can set a form to allow a new entry as soon as the submission has gone through from the Settings tab:
If you're looking for the form to remember a previous entry, there's another Idea already created around this topic! Please add your vote and voice to this thread: User-selectable "Sticky" Form Response Fields
Cheers,
Genevieve
Genevieve P.
Re: Comparing dates
Hi,
assuming your columns are formatted as dates, the below formula would work
=MIN(COLLECT([Date1]@row:[Date3]@row, [Date1]@row:[Date3]@row, >=TODAY()))
Etienne F.
Re: Created Date Issue
Hi @George Lie
System Date columns store date stamps as UTC, or GMT (although it can display the value in your local time).
This means that a formula looking at that cell will bring through the date value based on GMT, which is likely why you're seeing inconsistencies.
However, the Record Date Automation is based on local time! You could select a change in the Created column as your trigger, which only happens when the row is created. I would suggest using this workflow to populate a different date column, then reference this in your Start date column... or use it instead-of your Start date column? See: Set the Current Date with Record a Date Action
Cheers,
Genevieve
Genevieve P.
Re: Can I share the Workapps to collaborators and grant him the access as admin level?
To add group members in bulk you can copy and paste up to 1,000 email addresses from an Excel or .csv file into the "Add Members" tab. Here's more information:
Genevieve P.
Re: IF Formula Unparseable
As an extra credit followup to the above...
It looks like you are going to be creating a series of IF functions to display the month name from the Date in your date column. That is a great approach but there is a little trick you can do if you only want the 3-character month name. This will save you from having to nest IF functions.
This formula here...
=MID("JanFebMarAprMayJunJulAugSepOctNovDec", (MONTH(Date@row) * 3) - 2, 3)
takes the text string JanFebMarAprMayJunJulAugSepOctNovDec
And extracts a middle part of it using a little math based on the month number
This part here defines the start position within the chunk of text to extract:
(MONTH(Date@row) * 3) - 2
It says take the month number from the date and multiply it by 3, then subtract 2.
- So January is (1x3)-2 = 1
- February is (2x3)-2 = 4
- March is (3x3)-2 = 7
And if you look at the text string "Jan" starts at position 1, "Feb" starts at position 4, "Mar" starts at position 7...
The
,3
at the end of the function says to extract 2 characters.
- So for a date in January, it starts at position 1 and extracts 3 characters - Jan
- For a date in February, it starts at position 4 and extracts 3 characters - Feb
- For a date in March, it starts at position 7 and extracts 3 characters - Mar
I know you didn't ask about this, but thought it was a cool and helpful tip.
KPH