Formatting Dates for Error for Journal Entry

gholt

New Member
Joined
Aug 21, 2009
Messages
17
We create our journal entries on excel files. and to ensure that we are not picking up the previous' month or have an invalid date in the date field we wanted to create a visual for the user to see if the date showing in the field is not from a previous' monthly close or a future month.

So, our JV are created from about the 20th of the month to the 10th of the next month. So, for example if we were creating a JV on 4/22/10 to 5/10/10 we woudl be creating a JV for the month of April. So the user should input a date on the JV file from 4/22/10 - 4/30/10. Anything outside of this would highlight the field for error. This needs to be a carrying forward field that can be used for any month.

So, I have created a formula I think will capture the furture date, but I can't figure out what i'm not doing. Here is the formula: =IF((DAY(TODAY())/30)>0.67,IF((MONTH(TODAY())+30)=(MONTH($B$4)),21,13))

I am always getting the 13 as the value result. Any help would be appreciated.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I think your MONTH(TODAY())+30 is adding 30 months to today's month, and I guess you want to add 30 days to today's day.
You can also simplify elsewhere.
Try this.
Code:
=if(day(today())> 20,if(month(today()+30)=month($b$4),21,13))

You also need to specify what to do if Day(today())< 20, which seems to be missing.
 
Upvote 0
I think your MONTH(TODAY())+30 is adding 30 months to today's month, and I guess you want to add 30 days to today's day.
You can also simplify elsewhere.
Try this.
Code:
=if(day(today())> 20,if(month(today()+30)=month($b$4),21,13))

You also need to specify what to do if Day(today())< 20, which seems to be missing.

I took care of the < 20 through a conditional format. I'll try fixing the formula for the 30 day fix and see what that does.
 
Upvote 0
Thanks. I was able to use that in the conditional format to decide whether or not to highlight the field.

Thanks again Gerald.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top