Timesheet Formula Issues in Excel (Recurring Tasks)

[excelxzzx]

Hi guys,

I hope you are well.

For my company, I'm trying to create a dynamic time sheet either in excel, or google sheets as a free alternative to the online timesheet applications that you can use.

I've attached an excel which I'm using at the moment which will essentially allow me to fill in the 'schedule' tab, and it will populate the 'Weekly Schedule' tab. The problem is that in my company we run lots of reports (daily, weekly, monthly & quarterly). I would love it to have an option where I could get a task to recur every 5 weekdays, or once between certain dates every month. Does anyone know how I could do this? I'm not great on excel myself and I wouldn't really know where to start, or if it's even possible!

The plan would be to also merge this with a Gantt chart at some point to try and create a visual calendar which would contain manual tasks & recurring tasks for each employee.

Thanks!

[FDibbins]

If you are asking to add repeating tasks to the Input sheet, this "could" probably be done with formulas, but it would be tedious and would require all input fields to be pre-filled with formulas - which you would them manually over-write with data.

The other alternative would be to use VBA.

[Roel Jongman]

There is already a really nice UDF in the example file, I will have a look today if I can also make it work for recurring tasks..

[Roel Jongman]

So interim results.. I'm almost there I think, just want to check I'm on the right track..
I need to find a formula for handling workday frequency if needed (could be added later)
I need a small repair for when the start of the plan week is in a different month then the actual planned date (it is now sometimes 1 day off against the expected day)

What I did is to add 5 columns to the schedule
1. Period (Day(s), Week(s), month(s)
2. Frequency, every 1,2,3 / Day(s), Week(s), month(s)
3. Weekday - if you have a montly meeting the days would change because you are adding 30/31 days, if you want to stick to the same weekday for each monthly meeting you can pick a weekday there

4. Next start
5. Next End
This is where the magic happens in colum for next start Based on the chosen week in weekly schedule the formula will calculate the next meeting would be. The already existing formula on Weekly schedule (which is unchanged) will then place the meeting on the right line


Please look at the attached sheet and test if outcomes work