Help! I am trying to add dates to the week numbers in a Gantt sheet.

bud_brainerd

I have a project start date. I have a row for week numbers. I want to add a row below with dates. I cannot figure out how to write the formula, so I can drag it across.

KPH

You should be able to type the formula in the first column referencing the column name for that column and the row number for the row above. Then click on the cell and drag it to the right to copy the formula into the next column. As you do so, the column heading will update to use the new column name.

If this doesn't work, can you share a screenshot of your sheet so we can see your column headings and the formula?

bud_brainerd

Hi KPH, I have attached three screenshots. Two of my Excel spreadsheets showing the formulas. One of the smartsheet. Any help you can provide is greatly appreciated.

Excel Column E formula.PNG

Excel N-7 Screenshot with formula.PNG

smartsheet Capture.PNG

KPH

Hi @bud_brainerd

I have a few comments...

1 Sheet set up

When setting up a new sheet in smartsheet I suggest:

• Putting the rows you currently have in rows 1-6 in excel, onto one sheet
• Then in a different sheet
  • Making row 7 your column headings
  • Entering just data (i.e. row 8 onwards) in your sheet.

That will make using this sheet a lot easier in the long run. The two big problems you will face with the current set up are:

• Column types - you need the type of data in each cell within a column to be consistent. For example, you have a start date cell. That needs to be a Data format column if you want to do anything with this, such as adding or subtracting days. You therefore shouldn't put things that aren't dates into the same column.
• Formulas - it is a lot easier to reference an entire column and apply a formula to that than it is to reference a range of rows within the column. Smartsheets will add new rows to the bottom as needed. If you have a range of rows in your reference, this will not include new rows that are added.

Try to keep your data sheet as a simple sheet of columns. Add the meta data and metrics in a separate sheet.

2 Referencing cells in smartsheet

That being said...

If I have understood your screenshots correctly, you are wanting to put a formula in the cell I have smudged with yellow on this screen shot and want to reference the date I have circled in red, then your reference will need to include the column headings that I can't see. I have (badly) annotated these as Col 1, Col 2, Col 3.

The date is in a cell that would be referenced as: [Col 2]4

This is in the column called Col 2 and in row 4.

3 WEEKDAY with smartsheet v Excel

It looks like you want that cell I smudged in yellow to have the equivalate of an Excel =WEEKDAY(D5,3).

Excel

=WEEKDAY(D5)

in Smartsheet would be

=WEEKDAY([Col 2]4)

This returns the day of the week with 1 being Sunday, 7 being Saturday.

4 WEEKDAY Return Type 3 from Excel in Smartsheet

Excel enables you to define the start day. Return type 3 means the weekdays are 0 (Monday) to 6 (Sunday)

Smartsheet does not have this option, but we can workaround that by subtracting 2 from the weekday that is returned from the formula and using an IF to convert -1 to 6.

The formula would therefore be

=IF(WEEKDAY[Col 2]4 = 1, 6, WEEKDAY[Col2]4 - 2)

Here is an illustration of how that works