SOLVED

Time reset new date

%3CLINGO-SUB%20id%3D%22lingo-sub-1545743%22%20slang%3D%22en-US%22%3ETime%20reset%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1545743%22%20slang%3D%22en-US%22%3EHi%2C%20I%20have%20a%20spreadsheet%20with%20dates%20and%20times%20for%20appointments.%20Each%20day%20starts%20at%209am%2C%20is%20there%20any%20formula%20or%20conditionally%20formatting%20I%20can%20add%20to%20the%20cell%20so%20that%20the%20first%20appointment%20automatically%20resets%20to%209am%20when%20the%20date%20changes%20from%20the%20previous%20date%3F%20Thanks%20in%20advance%2C%20Dale%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1545743%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1545772%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20reset%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1545772%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739824%22%20target%3D%22_blank%22%3E%40Dalehab%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20answer%20to%20your%20question%20is%20a%20definitive%20%22Yes%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20your%20days%20are%20in%20column%20A%2C%20and%20A5%20starts%20a%20new%20day%20(i.e.%2C%20isn't%20the%20same%20as%20A4)%20then%20this%20formula%20will%20set%20B5%20to%209%3A00%20a.m.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(A5%26lt%3B%26gt%3BA4%2CTIME(9%2C0%2C0))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20would%20help%20me%20or%20somebody%20else%20here%20give%20a%20more%2C%20uh%2C%20detailed%20answer%20if%20you%20gave%20a%20little%20more%20details%20on%20how%20your%20spreadsheet%20is%20laid%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeally%2C%20post%20a%20copy%20of%20the%20spreadsheet%20itself%20(without%20any%20confidential%20info).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546327%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20reset%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546327%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20reply%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20using%20the%20spreadsheet%20to%20populate%20a%20calendar.%20Column%20A%20is%20the%20address%2C%20B%20is%20start%20date%2C%20C%20is%20End%20date%2C%20D%20start%20time%2C%20E%20is%20End%20time.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546331%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20reset%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546331%22%20slang%3D%22en-US%22%3EI%20should%20also%20add%2C%20I%20then%20need%20the%20same%20data%20in%20the%20below%20rows%20to%20calculate%20in%20half%20hour%20intervals%2C%20which%20I%20am%20using%20the%20formula%20%3DD1%2B0.5*(1%2F24)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546420%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20reset%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739824%22%20target%3D%22_blank%22%3E%40Dalehab%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20more%20specifically%20Excel-Time%20based%20way%20to%20do%20that%20is%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DD1%2BTIME(%2C30%2C)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20that%20way%20you%20could%20easily%20change%20your%20intervals%20to%20any%20increment%20of%20minutes%20desired.The%20syntax%20is%3CBR%20%2F%3ETIME(%3CEM%3Ehours%2Cminutes%2Cseconds%3C%2FEM%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(I%20read%20yesterday%20that%20one%20of%20the%20US%20Presidents%2C%20trained%20as%20an%20engineer%20and%20fanatical%20for%20efficiency%2C%20set%20his%20meeting%20calendar%20up%20for%208%20minute%20intervals.%20Of%20course%2C%2060%20minutes%20don't%20divide%20into%208%20minute%20intervals%3B%20so%20maybe%20he%20had%20a%20four%20minute%20break%20in%20there%20each%20hour)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548820%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20reset%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548820%22%20slang%3D%22en-US%22%3EHi%20mathetes%2C%3CBR%20%2F%3EI've%20attached%20an%20example%20of%20the%20spreadsheet%2C%20the%20time%20formula%20is%20great%2C%20however%2C%20I%20can't%20get%20the%20IF%20formula%20to%20work%2C%20not%20sure%20what%20I%20am%20doing%20wrong%3F%20%F0%9F%A4%A6%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548869%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20reset%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739824%22%20target%3D%22_blank%22%3E%40Dalehab%3C%2FA%3E%26nbsp%3BStarting%20in%20cell%20D3%20you%20want%20to%20paste%20the%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(B3%26lt%3B%26gt%3BC2%2CTIME(9%2C%2C)%2CE2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20this%20puts%20in%20an%20Excel%20Date%2FTime%20value%20that%20is%20equivalent%20to%200%20days%20and%20then%20only%20the%20time%2Fhours.%26nbsp%3B%20You%20could%20combine%20the%20Time%20values%20with%20the%20Start%20Date%20and%20End%20Date%20(is%20the%20End%20Date%20ever%20different%20than%20the%20Start%20Date%3F).%26nbsp%3B%20It%20all%20depends%20on%20what%20you%20want%20to%20do%20with%20the%20data%20and%20which%20might%20be%20easier%20for%20calculations%20down%20the%20line%20(if%20any).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549426%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20reset%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549426%22%20slang%3D%22en-US%22%3EThanks%20mtarler%2C%20much%20appreciated%3C%2FLINGO-BODY%3E
Occasional Contributor
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
7 Replies
Best Response confirmed by Dalehab (Occasional Contributor)
Solution

@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).

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.

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)

@Dalehab 

 

A more specifically Excel-Time based way to do that is

=D1+TIME(,30,)

 

In that way you could easily change your intervals to any increment of minutes desired.The syntax is
TIME(hours,minutes,seconds)

 

(I read yesterday that one of the US Presidents, trained as an engineer and fanatical for efficiency, set his meeting calendar up for 8 minute intervals. Of course, 60 minutes don't divide into 8 minute intervals; so maybe he had a four minute break in there each hour)

Hi mathetes,
I've attached an example of the spreadsheet, the time formula is great, however, I can't get the IF formula to work, not sure what I am doing wrong? 🤦

@Dalehab Starting in cell D3 you want to paste the formula:

 

=IF(B3<>C2,TIME(9,,),E2)

 

Note: this puts in an Excel Date/Time value that is equivalent to 0 days and then only the time/hours.  You could combine the Time values with the Start Date and End Date (is the End Date ever different than the Start Date?).  It all depends on what you want to do with the data and which might be easier for calculations down the line (if any).

Thanks mtarler, much appreciated