SOLVED

macro recording for recording and updating hour and minutes data to the current time

%3CLINGO-SUB%20id%3D%22lingo-sub-2791653%22%20slang%3D%22en-US%22%3Emacro%20recording%20for%20recording%20and%20updating%20hour%20and%20minutes%20data%20to%20the%20current%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2791653%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Microsoft%20Tech%20community%2C%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20screenshot%2C%20if%20I%20press%20the%20Enter%20data%20button%20it%20should%20add%20the%20hours%20and%20minutes%20values%20that%20are%20entered%20in%20the%20assigned%20cells%20to%20the%20current%20time%20cell%20and%20next%20time%20the%20user%20get%20another%20option%20to%20enter%20new%20set%20of%20values%20of%20hours%20and%20minutes%20and%20again%20when%20the%20button%20is%20pressed%20it%20should%20update%20the%20current%20time%20which%20was%20lastly%20updated%2C%20so%20that%20every%20time%20when%20a%20person%20enters%20a%20new%20set%20of%20hours%20and%20minutes%20he%20can%20see%20the%20last%20updated%20time.%20%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20ex-%3A%20current%20time%20is%2011%3A00AM%20and%20if%20I%20enter%202%20in%20hour%20and%2030%20in%20minutes%20and%20press%20enter%20data%20it%20should%20update%20to%201%3A30Pm%20and%20again%20if%20I%20enter%201%20in%20hour%20and%2030%20in%20minutes%20and%20press%20enter%20data%20it%20should%20be%203%3A00Pm%20and%20so%20on.%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20perform%20in%20excel%2C%20I%20hope%20I%20am%20clear%20with%20what%20I%20want%20to%20achieve%3F%3F%3C%2FP%3E%3CP%3EIf%20anyone%20could%20help%20me%20with%20this%20would%20be%20a%20great%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2791653%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2792409%22%20slang%3D%22en-US%22%3ERe%3A%20macro%20recording%20for%20recording%20and%20updating%20hour%20and%20minutes%20data%20to%20the%20current%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2792409%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20do%20it%20without%20you%20need%20use%20a%20VBA%2C%20but%20as%20you%20request%20it%20using%20vba...%3C%2FP%3E%3CP%3ECellWithHours%20and%26nbsp%3BCellWithMinutes%20are%20those%20ones%20where%20you%20input%20manually%20the%20Hours%20and%20the%20minutes.%3C%2FP%3E%3CP%3EStartDate%20is%20the%20cell%20reference%20you%20use%20on%20your%20spreadsheet.%3C%2FP%3E%3CP%3ERemember%20to%20change%20all%20of%20these%20variable%20names%20to%20the%20actual%20address%20of%20the%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYourOutputWillBe%3DDateAdd(%22h%22%2C%20CellWithHours%2C%20StartDate)%20%2B%20DateAdd(%22n%22%2C%20CellWithMinutes%2C%20StartDate)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2796392%22%20slang%3D%22en-US%22%3ERe%3A%20macro%20recording%20for%20recording%20and%20updating%20hour%20and%20minutes%20data%20to%20the%20current%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2796392%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20for%20the%20reply.%3CBR%20%2F%3EI%20tried%20using%20the%20formula%20but%20it%20dint%20work%20as%20expected%2C%20I%20guess%20there%20is%20something%20wrong%20in%20my%20syntax%20that%20I%20have%20used.%3CBR%20%2F%3EIn%20the%20attachment%20I%20expected%20the%20time%20to%20increase%20by%202hours%20and%2030%20minutes%20but%20it%20is%20showing%2012%3A30Pm%2C%20also%20I%20want%20the%20current%20date%20to%20change%20to%20next%20date%20once%20the%20time%20crosses%2012%3A00Am.%3CBR%20%2F%3ECan%20you%20please%20help%20me%20with%20the%20syntax%20based%20on%20the%20file%20I%20have%20shared.%20It%20would%20be%20really%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2796509%22%20slang%3D%22en-US%22%3ERe%3A%20macro%20recording%20for%20recording%20and%20updating%20hour%20and%20minutes%20data%20to%20the%20current%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2796509%22%20slang%3D%22en-US%22%3ETakes%20more%20time%20for%20us%20to%20understand%20your%20snapshots.%20I%20would%20suggest%20you%20post%20some%20sample%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2797030%22%20slang%3D%22en-US%22%3ERe%3A%20macro%20recording%20for%20recording%20and%20updating%20hour%20and%20minutes%20data%20to%20the%20current%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2797030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3Boh%20ok%2C%20I%20am%20attaching%20one%20here.%20Please%20have%20a%20look.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello Microsoft Tech community,

I have attached a screenshot, if I press the Enter data button it should add the hours and minutes values that are entered in the assigned cells to the current time cell and next time the user get another option to enter new set of values of hours and minutes and again when the button is pressed it should update the current time which was lastly updated, so that every time when a person enters a new set of hours and minutes he can see the last updated time.  

For ex-: current time is 11:00AM and if I enter 2 in hour and 30 in minutes and press enter data it should update to 1:30Pm and again if I enter 1 in hour and 30 in minutes and press enter data it should be 3:00Pm and so on.

Is it possible to perform in excel, I hope I am clear with what I want to achieve??

If anyone could help me with this would be a great help.

 

Thank you.

8 Replies

You can do it without you need use a VBA, but as you request it using vba...

CellWithHours and CellWithMinutes are those ones where you input manually the Hours and the minutes.

StartDate is the cell reference you use on your spreadsheet.

Remember to change all of these variable names to the actual address of the cell.

 

YourOutputWillBe=DateAdd("h", CellWithHours, StartDate) + DateAdd("n", CellWithMinutes, StartDate)

@Juliano-Petrukio 

thank you for the reply.
I tried using the formula but it dint work as expected, I guess there is something wrong in my syntax that I have used.
In the attachment I expected the time to increase by 2hours and 30 minutes but it is showing 12:30Pm, also I want the current date to change to next date once the time crosses 12:00Am.
Can you please help me with the syntax based on the file I have shared. It would be really helpful.

 

Thank you.

Takes more time for us to understand your snapshots. I would suggest you post some sample file.

@Juliano-Petrukio oh ok, I am attaching one here. Please have a look.

 

 

Thank you.

best response confirmed by UG_1180 (Occasional Contributor)
Solution

@UG_1180 

Range("E6").Value = Range("B6") + TimeSerial(Range("E10").Value, Range("E11").Value, 0)

 

If it worked for you, dont forget to flag it as solved.

Remember that hitting the like button is less costly than buying me a coffee.

 

1thank you guys so much take a picture of my work picture a camera a camera camera camera camera

smi Daisy Liangthankthank youthank you so much I said lainDaisy PeytonCaden CadenEnglish so much so much my Chanel and Mason Mason C Mason's taking you guys so much thank you for the book I see you guys tomorrow morning I-85 at 8:30 I-85 845if he has so much as you guys next videofamous tube kids famous tube kids Batman suit kids famous to get famous two kids Batman suitcasessis vs. bro Ronald sis versus bro versus sis versus brocall Mike by Mike by Mike and MikeElsa and Anna Elsa and Anna for school tomorrow on Thursday at 8:45 at 8:30 thank you guys so much I'll see you guys tomorrow have a good night TV thank you guys so much I hope you guys have a great weekend if you're free on Friday thank you guys so much have a good weekend thank you so much you're the best one in the whole wide world by Melissa Ramsey tomorrow at 7 I'm miserable around by miserable well Daisy by Daisy Daisy Daisy Daisy and land land land land land land so much power we have a good day as yes Mom

  1.  

@Juliano-Petrukio 

Yes thank you so much this is what I was expecting.

One more thing if you can help to do would be great is to update the date as well as soon as the time crosses 12:00Am with reference to the current date.

 

For ex-: if the start time is 11:00Pm and start date is 1st Jan'21 and I want to update 10 hours and 30 minutes which means it goes to the next day 09:30AM on 2nd Jan'21 so the current date should display 02-01-2021.  

 

Thank You.