Forum Discussion

Copper Contributor

Jul 25, 2020

Solved

Time reset new date

Hi, I have a spreadsheet with dates and times for appointments. Each day starts at 9am, is there any formula or conditionally formatting I can add to the cell so that the first appointment automatically resets to 9am when the date changes from the previous date? Thanks in advance, Dale

excel

mathetes
Jul 25, 2020
Dalehab

The answer to your question is a definitive "Yes"

For example, if your days are in column A, and A5 starts a new day (i.e., isn't the same as A4) then this formula will set B5 to 9:00 a.m.
=IF(A5<>A4,TIME(9,0,0))

But it would help me or somebody else here give a more, uh, detailed answer if you gave a little more details on how your spreadsheet is laid out.

Ideally, post a copy of the spreadsheet itself (without any confidential info).

7 Replies

mathetes
Silver Contributor
Jul 25, 2020
Dalehab

The answer to your question is a definitive "Yes"

For example, if your days are in column A, and A5 starts a new day (i.e., isn't the same as A4) then this formula will set B5 to 9:00 a.m.
=IF(A5<>A4,TIME(9,0,0))

But it would help me or somebody else here give a more, uh, detailed answer if you gave a little more details on how your spreadsheet is laid out.

Ideally, post a copy of the spreadsheet itself (without any confidential info).
- Dalehab
  Copper Contributor
  Jul 26, 2020
  Thanks for the reply mathetes.
  
  I'm using the spreadsheet to populate a calendar. Column A is the address, B is start date, C is End date, D start time, E is End time.
  - Dalehab
    Copper Contributor
    Jul 26, 2020
    I should also add, I then need the same data in the below rows to calculate in half hour intervals, which I am using the formula =D1+0.5*(1/24)