Return the day of yesterday if a cell is updated after midnight

In a google sheets document I implemented a script where the Sheet name is changed based on the content in cell A10. This is the script:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var oldName = sheet.getName();
  var newName = sheet.getRange(1,10).getValue();
  if (newName.toString().length>0 && newName !== oldName) {
    sheet.setName(newName);
  }
}

In cell A10 I have the formula =concatenate(A1:A9). In A1 I have the formula =day(today()). A2 = month(today()). A3 = year(today()). Then some static text in the next cells and in A9 is the input that will trigger the filename to be changed.

In this way, the sheet name is changed automatically with the current day, month, year, based on the time when the A9 cell content is changed.

I have 3 sheets, that are changed in the morning, afternoon and night. With the morning and afternoon sheets, there is no problem, but for the night, because it can be changed either before or after midnight, I would like to use the same day as the other 2 sheets, no matter the time when it is changed.

I already tried this formula for the day cell in the night sheet, but without success:

A1=if(day(today)='Afternoon'!A1; day(today()) ; day((today())-1))

Is there a way how to refine this formula, or should I take a different approach?

Thank you!

Solution

Evening Day Value

=LET(now, NOW(),
   DAY(now-(MOD(now, 24)<12)))

Subtracts 1 day if the time is before 12:00 PM (noon).
Since 1 day=1 and 1 hour=1/24 it follows that MOD(x, 24)=12 where x=12:00 PM

Single Date String Formulas

As @Tedinoz pointed out, addressing the day in the absence of the date, is prone to errors with the day, the month, and the year:

DAY("1/1/23")-1 ≠ DAY("1/1/23"-1)
MONTH("1/1/23") ≠ MONTH("1/1/23"-1)
YEAR("1/1/23") ≠ YEAR("1/1/23"-1)

The three sheets should each use a single formula to return the date string.

Evening Date String

=LET(now, NOW(),
   TEXT(
     now-(MOD(now, 24)<12), 
     "ddmmyyyy"))

Replaces the three formulas in A1:A3.
Tweak the date string format as you prefer.

Morning/Afternoon Date String

=TEXT(NOW(), "ddmmyyyy")

Replaces the three formulas in A1:A3.
Strips off the time using a single date format.
Tweak date string format as you prefer.