Is there any function to add date to specific cells?-CodePudding

I neeed some help about my company staff attendance record form. The form is like this Many cells are missing date, for example, some cells of BE column miss 1.24, I want to create a function that can pop up a box and I can enter the date[1.24], but I do not want to add 1.24 to each cell; I want to add 1.24 to these cells that miss the date. Does this can be achieved? Thank u so much for helping me!

CodePudding user response：

Yes, you can achieve this using a macro in Microsoft Excel. Macros are a set of instructions that automate tasks in Excel. You can create a macro to add the date you enter to specific cells in a column. Here's how you can create a macro in Excel:

Open the Microsoft Excel workbook containing the form you want to add a date to. Press Alt F11 to open the VBA Editor. In the VBA Editor, select the workbook that contains the form from the Project Explorer window on the left. From the Insert menu, select Module. A new module will appear in the Code window. Copy and paste the following code into the module: Sub AddDateToCell() Dim rng As Range Dim dateInput As Variant Set rng = Application.InputBox("Select the cells where you want to add the date", Type:=8) dateInput = Application.InputBox("Enter the date you want to add to the cells", Type:=2) rng.Value = dateInput End Sub Save the macro by clicking the Save button on the toolbar or by selecting Save from the File menu. Close the VBA Editor by clicking the X in the top right corner. Return to the Excel workbook and select the cells that are missing the date. From the Developer tab, select Macros. Select the AddDateToCell macro from the list and click the Run button. In the Input Box, enter the date you want to add to the cells. The date will be added to the cells you selected. Note: The above code assumes that the cells you want to add the date to are empty. If they contain data, the data will be overwritten by the date you enter.

CodePudding user response：

Yes, you can add a date to specific cells in Google Sheets using a script. To do this, you'll need to use the prompt function to create a dialog box that will ask you to input the date, and the getActiveRange function to identify the cells that you want to add the date to. Here's an example of what the code might look like:

function addDate() {

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getActiveRange();

var date = new Date(prompt("Enter the date in format YYYY-MM-DD:", ""));

range.setValue(date); }

You can then add this function to your Google Sheet by going to Tools > Script editor in the menu. In the script editor, you can run the function by clicking the play button or by going to Run > addDate.

This will add the date to the active cells in the sheet. If you want to only add the date to specific cells, you'll need to modify the range variable to only include those cells.