Import of Excel File

Majid Kayani

Time Sheet Proposal.xlsx

@Paul Newcome Hi Paul hope you don't mind me tagging you into this.

the attached file with the tab called "live recording" is the file i am trying to import into Smartsheet's. the issues I am having ( a big issue ) once I import this file into timesheets all my formulas are #UNPARSEABLE.

I am really struggling to understand and the setup I have made in excel is what I need.

any suggestions or fixed I am open to them all :)

Majid Kayani

I would be great if something could assist me with my query.

Genevieve P.

Hi @Majid Kayani

Smartsheet and Excel have different capabilities and functions, formula structures are different between the two programs so it's likely you'll need to either re-write any formulas in Smartsheet or use a different capability to create something similar.

For example, I see you have a "Date Started" column in your sheet. Instead of using a formula, you could set up a Record a Date workflow in your sheet to automatically populate a date in a Date column when something happens (such as when a Status cell is changed to a specific value). See: Set the current date with the record a data action

For the "Start Time" column, Smartsheet does not currently have a Time function or Time column. You may want to look into adding a System Date Created column which would populate both a Date and Timestamp for when a row was created: Use a System Column to Automatically Add Information to a Row

For the "Stop Recording" date, I would again advise using a Record a Date workflow to populate the date when "Stop Recording" has a value of "Yes".

"Time Spent" will be trickier as there is no Time function in Smartsheet. You could use a NETWORKDAYS formula to find the number of working days between your two dates, if that would help: NETWORKDAYS Function

For the "Billable" column, would you mind explaining what it is you want the formula to do? If you can write out the logic of the formula we could look to see if there are functions in Smartsheet to achieve your goals.

Here are some free webinars on Formulas that you may find helpful as well: Formulas webinar series

Cheers,

Genevieve

Majid Kayani

Thank you so much for you reply I really appreciate it. i will reply shortly with a response in regards to billable tab.

Once again thank you so much for taking the time to respond.

Majid Kayani

Hi @Genevieve P. Thank you so much for you reply I really appreciate it.

what is trying to do with the billable column is link to the Task Column in excel I have set it up that is once a task has been chosen ( internal Project or External Project) it automatically chosen if it is billable or not. In this case it would be

Internal Project = Yes

External Project = No

please find below the formula:

=IF(ISERROR(OFFSET(Sheet2!$E$7,1,MATCH('Live Recording '!$O2,Sheet2!$E$7:$F$7,0)-1,1,1)),"",(OFFSET(Sheet2!$E$7,1,MATCH('Live Recording '!$O2,Sheet2!$E$7:$F$7,0)-1,1,1)))

Once again thank you so much for taking the time to respond.

Genevieve P.

Hi @Majid Kayani

It sounds like you want a simple IF statement that says IF the cell says "Internal Project", then return "Yes", otherwise return "No". See: IF Function

Try this:

=IF(Task@row = "Internal Project", "Yes", "No")

Keep in mind that if any of the cells in your Task column are blank, then it will say "no". You could adjust this by having a blank cell be blank:

=IF(Task@row = "Internal Project", "Yes", IF(Task@row = "External Project", "No", ""))

You can make this into a Column Formula.

An alternative would be to set a Change Cell workflow so that when a Task is set to a specific value, it automatically changes the cell in your Billable column. See: Change the Value of a Cell in an Automated Workflow

Cheers,

Genevieve

Majid Kayani

Hi @Genevieve P.

Thank you for the information once again I really appreciate it.

All the advice given to me has been a massive help. I am now going to work on this on the weekend.

would it be okay to reach out to you again if I have any queries?

kind regards :)😊

Genevieve P.

Hi @Majid Kayani

Of course! Feel free to create a new Question in the Community and either myself or our other Community members will be happy to help.

I would also recommend reviewing any of the free Onboarding Webinars that you may find helpful, and Best Practice Webinars, here.

Cheers,

Genevieve