当我在单元格A中输入值时,如何在Google工作表中自动填充日期 [英] How to autofill date in google sheet when i enter value in cell A
本文介绍了当我在单元格A中输入值时,如何在Google工作表中自动填充日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果我在工作表的单元格A中输入付费"文本,我想自动拉出单元格B中的当前日期.如果单元格为空,则不应拉出日期.喜欢:
If I enter "paid" text in cell A in sheet, I want to auto pull the current date in cell B. If the cell is empty, it should not pull the date. Like:
--------------------------------------
A | B
--------------------------------------
Paid | 2/25/2018
----------------------------------
Paid | 2/26/2018
----------------------------------
empty cell |
----------------------------------
empty cell |
I tried to use =ArrayFormula(IF(A2:A81=paid),"paid",(NOW()))
但是出现错误.他们有办法吗?
But getting error. Is their any way to do it?
推荐答案
尝试一下:
=ArrayFormula(IF(A2:A81="paid", NOW(),""))
如果您不希望更改日期,则需要使用以下脚本:
If you never want the date to change, you need to use script like this:
function onEdit() {
// writes the current date to the cell to the right on the row when a cell in a specific column is edited
// adjust the following variables to fit your needs
var sheetNameToWatch = "Sheet1";
var columnNumberToWatch = /* column A */ 1; // column A = 1, B = 2, etc.
var valueToWatch="paid";
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveCell();
var val=sheet.getActiveCell().getValue()
if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && val==valueToWatch) {
var targetCell = sheet.getRange(range.getRow(), range.getColumn()+1
);
targetCell.setValue("" + Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy"));
}
}
没有一种方法可以通过我所知道的公式来完成.可以将其缩短,但仍需要脚本:
There is not a way to do this with a formula that I know of. It can be made shorter, but script is still required:
function onEdit(e) {
if (e.source.getSheetName()=="Sheet1" && e.range.getColumn() == 1 && e.range.getValue() == "paid") {
e.range.offset(0,1).setValue(new Date());
}}
这篇关于当我在单元格A中输入值时,如何在Google工作表中自动填充日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文