Hi guys,
Does anyone knows the formula i can use in google sheets to get same date and time for in sequence. For example I can use EDATE atm but that shows everything at 0:00:00 hours, I am interested to get same day of the month and as well as same time as reference cell. So say For Jan i have 20/01/2022 10:00:00, I need 20/2/2022 10:00:00 for Feb, March and so on
Thanks
=EDATE(C6,1) + (C6 - INT(C6))
This actually is adding another day for me
Yikes! Let me look at it again. I just did it on the fly without using Google Sheets to test it on.
So, you are saying that you input the day/time as say: 10/1/2022 12:07:00 and it is returning 11/2/2022 12:07:00??
Edit: I just tried it on Google Sheets with that date and time I gave in my example, and it returns the same day, year and time, but the month has increased by 1.
Edit: 2: Just tried it in Open Office, Libre Office and Office 2019, and all returned the correct day/year/time values + 1 month.
Thats my bad. It was in MM/DD/YY format, I read it wrong. Its all good. Thank you
Helper cell column for the dates?
then concatenate the date from the Helper column to a hardcoded time?
=EDATE(B2,1)+(B2-ROUNDDOWN(B2,0))
Where B2 is the orginal reference cell
Thank you
I'd try filling in the first three by hand, then selecting those three and dragging down. Usually it's smart enough to fill it all out how you want.
No good if you need it to change all of them at once and they're dependent on the first cell. Your way you would literally have to manually do it every time.
=EDATE(C6,1) [C6 being the reference cell]
Format the cells to DD/MM/YYYY hh:mm:ss
Strip the time out and place it back as separate: =C6-INT(C6)
You could try:
Where C6 is YOUR cell reference.