Current Month/Year instead of January 1900

Question

I got range of cells with set formatting > "Date", and by default when I'm typing any number it is converted to date with day I've typed and January 1900.

How to change it so I would get always current month and year after typing just numbers of day ?

How to do this in LibreOffice Calc aswell ?

Answer 1

This is for Excel. This example uses the range from A1 through A10 Place the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, rint As Range, r As Range
    Set rng = Range("A1:A10")
    Set rint = Intersect(rng, Target)

    For Each r In rint
        Application.EnableEvents = False
            r.Value = DateSerial(Year(Date), Month(Date), r.Value)
        Application.EnableEvents = True
    Next r
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

The code takes your typed value and replaces it with the properly constructed date. You can change the range to suite your needs.

Answer 2

The solution from @Gary's Student can be adapted for LibreOffice as follows.

Sub ContentChangedHandler(oChangedRange As Object)
    Dim oSheet As Object
    Dim oCheckingRange As Object
    Dim oIntersectRanges As Object
    Dim oIntersectRange As Object
    Dim oData()
    Dim oRow()
    Dim i%, j%, k%
    oSheet = ThisComponent.getSheets().getByIndex(0)
    oCheckingRange = oSheet.getCellRangeByName("A1:A10").getRangeAddress()
    oIntersectRanges = oChangedRange.queryIntersection(oCheckingRange)
    For i = 0 to oIntersectRanges.getCount() - 1
        oIntersectRange = oIntersectRanges.getByIndex(i)
        oData() = oIntersectRange.getDataArray()
        For j = LBound(oData()) To UBound(oData())
            oRow() = oData(j)
            For k = LBound(oRow()) To UBound(oRow())
                oRow(k) = CLng(DateSerial(Year(Date), Month(Date), oRow(k)))
            Next
        Next
        oIntersectRange.setDataArray(oData())
    Next
End Sub

To set up the event, right-click on the sheet tab and choose Sheet Events. Assign the Content changed event to the macro above.

Be sure to format the cells as dates by going to Format -> Cells, or else they will be displayed as ordinary numbers.