Creating a custom date-based calculation table in Google Sheets

Other Questions Google Sheets
1

Hi everyone! I’m stuck on making a special table in Google Sheets. Here’s what I need:

  1. Input fields for start date, end date, and daily units
  2. Calculate total days between dates (including start and end)
  3. Multiply daily units by total days
  4. Distribute total units across 12 months, considering actual days per month

I’m not great with spreadsheets, so any help would be awesome! Thanks in advance for your tips and tricks.

Here’s a basic setup I tried:

| Start Date | End Date | Units/Day | Total Days | Total Units |
|------------|----------|-----------|------------|-------------|
| [Input]    | [Input]  | [Input]   | [Formula]  | [Formula]   |

But I’m lost on how to split the units by month accurately. Any ideas?

2

hey dave, i’ve done smthing similar before. here’s a quick tip: use SEQUENCE to generate a list of months between ur dates. then use SUMPRODUCT with some clever conditions to count days per month. for units, just multiply by ur daily rate. it’s not too hard once u get the hang of it!

3

I’ve tackled a similar project before, and here’s what worked for me:

First, use the DATEDIF function to calculate total days between dates. Then, create a column for each month and use a combination of EOMONTH and MIN functions to determine the number of days falling within each month.

For distributing units, multiply your daily units by the days in each month column. This approach ensures accurate distribution based on the actual days per month.

One tricky part is handling partial months at the start and end of your date range. You might need to use IF statements to adjust the first and last months accordingly.

It’s a bit complex, but once set up, it’s quite powerful. Let me know if you need more specific formula examples.

4

As someone who’s worked extensively with Google Sheets for project planning, I can relate to your challenge. Here’s a method that’s served me well:

Start by setting up your input cells as you’ve done. For the total days calculation, use =DATEDIF(A2,B2,“D”)+1 to include both start and end dates.

The tricky part is indeed the monthly distribution. I’ve found success using an array formula to generate a row for each month between the start and end dates. Then, use SUMIFS to count the days falling in each month.

For the units distribution, multiply your daily units by the days in each month. This approach gives you an accurate monthly breakdown without manual calculations.

One tip: Add data validation to your date inputs to prevent errors. It’s saved me countless headaches!

Remember, the setup takes some time, but it pays off in the long run with its flexibility and accuracy.