Date Formula

Rainier Hollands

Hi everyone,

Is there a formula in which I can calculate the number of days in any given month?

Regards

Rainier

Paul Newcome

You first need to calculate the first day of the month.

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Then you calculate the first of the next month and subtract 1.

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Subtract the first from the second, and that tells you how many days are in the current month.

=(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1) - DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

The number of days into the month for today would be

=DAY(TODAY())

So you take the number of loads

=[Number of Loads]@row

Divide it by the number of days already passed in the month

=([Number of Loads]@row / DAY(TODAY()))

And then multiply by the total number of days in the month.

=([Number of Loads]@row / DAY(TODAY())) * ((IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1) - DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

Debbie Sawyer

There are lots of date functions available, can you explain a little further the scenario you are looking to use this formula in? (to give us a little context)

If it is just how many days in January, how many days in February etc are you able to set aside 24 cells either in the current sheet (in hidden columns) or another sheet and just write up the months, the number of days and use the mini table as a Vlookup master/ Index(Match()) type solution...

Thanks

Debbie

Rainier Hollands

I'm busy building a end of month forecast.

So I'm trying to say, take the total amount of loads the trucks have done this month so far, divide it by the amount of days there have been in the month already and multiply it by the number of days in that month.

I hope that makes sense, if there isn't a formula, I'll just set up the table then thank you!

Paul Newcome

You first need to calculate the first day of the month.

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Then you calculate the first of the next month and subtract 1.

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Subtract the first from the second, and that tells you how many days are in the current month.

=(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1) - DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

The number of days into the month for today would be

=DAY(TODAY())

So you take the number of loads

=[Number of Loads]@row

Divide it by the number of days already passed in the month

=([Number of Loads]@row / DAY(TODAY()))

And then multiply by the total number of days in the month.

=([Number of Loads]@row / DAY(TODAY())) * ((IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1) - DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

Rainier Hollands

Hi Paul,

Thank you so much for taking your time out to help me. The formula worked perfectly! Have a great day!

Regards

Rainier

Paul Newcome

@Rainier Hollands Happy to help! 👍️

@Debbie Sawyer A quick note about the method I believe you were working towards (table and INDEX/MATCH or VLOOKUP):

You would actually need 3 columns to be completely accurate in calculations if there are going to be multiple years on the sheet if you didn't want to have to try to remember to update the table. You would need one for regular years and one for leap years (plus the reference column of course). I know I would forget to update the table. Haha.

From there you would need to find a way to figure out if the year for the calculations is a leap year or not (a basic MOD function makes this pretty straightforward when put into an IF) to automate the column number to pull from.

I have something very similar set up in a sheet and ran into the leap year issue the hard way. Haha

Debbie Sawyer

@Paul Newcome sure - so much to remember! covering all angles etc. I liked your result though - great stuff!

Paul Newcome

@Debbie Sawyer I work A LOT with dates in Smartsheet. Also, my team has a habit of taking that one scenario that should never happen and making it a reality. Haha. My managers and I had a discussion one day about a very specific scenario. The result was that it would literally never happen because it just made absolutely no sense. Well I kid you not, it was the very next day that Sales came to us with 3 projects that fell into the "never going to happen" bucket that had just been discussed. Needless to say, we have gotten very good over here at finding all kinds of angles to cover. Haha

Debbie Sawyer

😁

Rainier Hollands

@Paul Newcome

Hi Paul,

So initially I thought the formula was working, however when I tested it individually, it returns "29" however there are 31 days in the month of March, or is it picking up the Month of February?

This is the formula we using.

((IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1) - DATE(YEAR(TODAY()), MONTH(TODAY()), 1)))

Rainier

Paul Newcome

This would be the portion to pull the last day of the current month...

IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Rainier Hollands

@Paul Newcome

Hi Paul,

Thank you so much for your solution! Worked great!

Regards

Rainier

Paul Newcome

Happy to help! 👍️