Returning date format to text in additional column

Kristin Badalow
Kristin Badalow ✭
in Formulas and Functions

Hello,

Is there a way to return a date formatted column to an additional text column using formulas? I'm looking to create a report. It's not letting me use the "date" formatted field. That field needs to remain in date format, so was looking to add an additional column so it will automatically populate the date into text format in an additional column that can be hidden not to interfere with the sheet.

Thanks in advance.

· Share on FacebookShare on Twitter

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What do you mean it won't let you use the date formatted field? What exactly are you trying to do with it in the report?

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Kristin Badalow
    Kristin Badalow ✭

    With the report, I need to calculate how many tasks I have for a given week. The dates are listed separately. So i was thinking about using the report builder to build date frames and get a count that way. If you have a better way of doing that, i'm completely open to it. That is also a struggle with my assignment. I'm trying to think of ways to go about it. The ultimate goal is to produce a report on a dashboard showing tasks per week.

    Thank you,

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Unfortunately you cannot use report data to generate counts using cross sheet formulas or cell linking, so even if you were to convert it into a text string for the report, it still wouldn't be able to be used the way you are wanting.


    If you want these calculations separate from the source sheet, then you will need to create another sheet and use cell linking and/or formulas containing cross sheet references.


    There are a number of ways to set it up. If you would like to give a visual example of some mocked up data that looks like what you are trying to accomplish (manually entered for now of course), I would be happy to try to help you work up some solutions.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Kristin Badalow
    Kristin Badalow ✭

    Here is a sample of what i'm working with. The primary column is filled with misc things that need to get done. Nothing of relevance to my assignment. I need a full count by week (Monday-Sunday), and a count of each type of change. This has previously been done on excel, but information is stored in smartsheet. I would like to show some sort of report on the dashboard so this doesn't have to be so manual.


    image.png


    Excel info

    image.png


    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. In Smartsheet the easiest way to do this would be to have two separate date columns on your metrics sheet. One for the start of the week and the other for the end of the week.


    Then you would use the appropriate steps for cross sheet references in formulas such as...

    =COUNTIFS({Source Sheet Type of Change Column}, CONTAINS("Routine", @cell), {Source Sheet Date Column}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row))


    Update "Routine" for each of your types, then you can use something like this in your Total column:

    =SUM(Routine@row:Emergency@row)

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!