First Day of the Prior Month and Last Day of Current Month

cghallo_UCDenver

Hello! I have a date (A below) that pulls as expected. In B, I'd like to grab the first day of the month for the prior month and then C would be the last day of A. I have room for additional helper columns as needed to make it work. Thanks, Community!

Kaveri Vipat

Hi cghallo_UCDenver, 

You can try this formula for the first day of the prior month- 

=DATE(YEAR([Shift Date]@row), MONTH([Shift Date]@row) - 1, 1) 

And for the last day of the current month- 

=IFERROR(DATE(YEAR([Shift Date]@row), MONTH([Shift Date]@row) + 1, 1), DATE(YEAR([Shift Date]@row) + 1, 1, 1)) - 1 

I hope this helps!

cghallo_UCDenver

Thank you, @Kaveri Vipat...it worked as expected - much appreciated!

Kaveri Vipat

Hi cghallo_UCDenver, 

You can try this formula for the first day of the prior month- 

=DATE(YEAR([Shift Date]@row), MONTH([Shift Date]@row) - 1, 1) 

And for the last day of the current month- 

=IFERROR(DATE(YEAR([Shift Date]@row), MONTH([Shift Date]@row) + 1, 1), DATE(YEAR([Shift Date]@row) + 1, 1, 1)) - 1 

I hope this helps!

cghallo_UCDenver

Thank you, @Kaveri Vipat...it worked as expected - much appreciated!

cghallo_UCDenver

@Kaveri Vipat, I tried to replicate using the above, which worked. Can you please provide the syntax if, in Check Date, I wanted the last day of the next month of the Earnings End Date. For example, the 7/1/23 should be 7/31/23. Thank you!

Kaveri Vipat

Hi cghallo_UCDenver,

I am glad it worked, You can try this formula to get the last day of next month.

=IFERROR(DATE(YEAR([Earnings End Date]@row), MONTH([Earnings End Date]@row) + 2, 1), DATE(YEAR([Earnings End Date]@row) + 1, 1, 1)) - 1

I hope this works for you!