Default Value for date cell

M

mrbugs

New Member
Joined
May 27, 2008
Messages
10
Hi,

I am working on a sheet that has some special formulas that use a date column for calculation. The date must be the first of the given month for the calculation to work properly. The sales folks are not completing the sheet properly which is causing a lot of manual corrections/calculations.

2 questions

1) Is there a way that I can have a default value in a date cell so the calculation will always work? For example, it would automatically have 1/1/2008 if they didn't put anything into the cell? Some of the folks are not putting a date in which causes the formula to go haywire and not roll up properly giving me "value" error message.

2) Is there a way that could force a date to be typed in that is the first day of a given month? So if they typed 1/15/2009, it would change to 1/1/2009?

Thanks as always for any help you can offer.

Bugs
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Hello,

1) With this one, where ever you formula is you could do this:

Let say the formula was located in C1 and was A1 * B1 then you would put in c1 the formula below

=IF(A1="","01/01/08",A1)*B2

2) You can use Validation. If it the 1st of the month each time (month) and you want it to have the 01/01/08. Use Validation under the Data menu but first select A1 then do this. In the settings tab, Select Allow Date, the choose your options. E.g Use Between for 01/01/08 to 01/01/08 and this will allow only 1st for that cell. If you want to aid the user by showing a message then use the error alert tab. Then just copy Cell A1 down the spreadsheet and it will stop anything other than what you want them to type in.

Hope this helps

Mike
 
Upvote 0
Hi mrbugs:

Let us address your 2nd question ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
AB
215-Jan-20091-Jan-2009
Sheet3


</body></html>

I hope this helps.
 
Upvote 0
Hi MrBugs,

I don't know if you're familiar with VBA, but thats what you need. Copy the following code into the Worksheet object of the sheet where the dates are entered.


Assuming there has to be entered data into column B to get a date into column A :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim vMonth As Variant
Dim vYear As Variant

    If Not Intersect(Range("B3:B14"), Target) Is Nothing Then
        vMonth = Month(Now)
        vYear = Year(Now)
        If IsEmpty(Target.Offset(0, -1)) Then
            Target.Offset(0, -1).Value = DateSerial(vYear, vMonth, 1)
        End If
    End If
    
    If Not Intersect(Range("A3:A14"), Target) Is Nothing Then
        vMonth = Month(Target.Value)
        vYear = Year(Target.Value)
        Target.Value = DateSerial(vYear, vMonth, 1)
    End If

End Sub

Erik
 
Upvote 0
You can use Data > Validation to ensure that the only permitted entries are dates that are 1st of the month

Assuming your date cell is A1

Select A1

Data > Validation > under "Allow" select "Custom" and in the formula box enter the formula

=DAY(A1)=1

Under "Error alert" you can enter a suitable message for the user e.g. "Only permitted entry is a date - 1st of the month only"
 
Upvote 0
You must log in or register to reply here.

Similar threads

tourless
  • Question
MTD Formula not working
Replies
6
Views
74
awoohaw
awoohaw
M
  • Solved
Formula to 2-way look up a unique identifier and a date, then take the highest number from another column
Replies
3
Views
63
MrsFraser07
M
K
  • Solved
Prorating Salary based on hire date and Term date - by month
Replies
4
Views
131
KH Consulting
K
R
  • Question
How do you get third Wednesday of the month that isn't dependent on another cell
Replies
6
Views
103
redinator
R
P
  • Question
Did I Break the MONTH Function with the TEXT Function?
Replies
3
Views
195
preitano
P

Forum statistics

Threads
1,214,973
Messages
6,122,534
Members
449,088
Latest member
RandomExceller01

Share this page

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