SOLVED

Add time

%3CLINGO-SUB%20id%3D%22lingo-sub-1785551%22%20slang%3D%22en-US%22%3EAdd%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1785551%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20work%20out%20a%20formula%20to%20add%20Time%20to%20a%20time%20if%20it%20is%20later%20than%20a%20set%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20example%3A%20If%20a%20job%20is%20going%20to%20start%20at%20or%20after%2021%3A30%20I%20need%20to%20move%20the%20start%20time%20to%2006%3A00%20the%20following%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eany%20help%20would%20be%20much%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1785551%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1785937%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1785937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834154%22%20target%3D%22_blank%22%3E%40Nick1960%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20a%20start%20time%20is%20in%20cell%20D2.%3C%2FP%3E%0A%3CP%3EIn%20another%20cell%2C%20e.g.%20E2%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(MOD(D2%2C1)%26gt%3BTIME(21%2C30%2C0)%2CINT(D2)%2B1%2BTIME(6%2C0%2C0)%2CD2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20the%20cell%20with%20the%20formula%20with%20the%20desired%20date%2Ftime%20format.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to work out a formula to add Time to a time if it is later than a set time.

 

 

for example: If a job is going to start at or after 21:30 I need to move the start time to 06:00 the following day.

 

any help would be much appreciated

 

 

2 Replies
Best Response confirmed by Nick1960 (New Contributor)
Solution

@Nick1960 

Let's say a start time is in cell D2.

In another cell, e.g. E2, enter the formula

 

=IF(MOD(D2,1)>TIME(21,30,0),INT(D2)+1+TIME(6,0,0),D2)

 

Format the cell with the formula with the desired date/time format.

@Hans Vogelaar 

Hi Hans,

 

Thanks very much, works a treat. Much appreciated

 

Cheers