SOLVED

How to have date change when time changes

Copper Contributor

I am trying to find out how to have a date update when my time is updated. So basically, I want the current date and time, then when adding a set amount of time (ex. today is 08/23/2023 1911hrs, and my formula adds time additional time to where it is now 0130hrs) it will not bring the cell to the updated date of 08/24/2023 0130hrs.  My formula is the following so far, =TODAY()+TIME(HOUR(NOW())+B3,MINUTE(NOW())+C3,SECOND(NOW())+D3) , I do understand my formula may be wrong. Still, I can not find anything explaining how to update the date given the additional time added. I hope I explained it well enough for someone to help. I can give you a copy of the spreadsheet if needed. 

 

2 Replies
best response confirmed by JSONDAVID1 (Copper Contributor)
Solution
I don't understand what is in B3, C3, and D3 but I will assume they are the #hrs, #mins, and #secs you want to add to NOW(). The function TIME() will only return a TIME() value (i.e. always <1 day) so =TIME(25,0,0) is 0100 not 2500, so TODAY()+TIME(25,0,0) is the same as TODAY()+TIME(1,0,0)
Try this instead:
=NOW()+B3/24 + C3/24/60 + D3/24/3600
Thank you so much. Been trying to figure this out for the last 8 hours. Thank you!!!!
1 best response

Accepted Solutions
best response confirmed by JSONDAVID1 (Copper Contributor)
Solution
I don't understand what is in B3, C3, and D3 but I will assume they are the #hrs, #mins, and #secs you want to add to NOW(). The function TIME() will only return a TIME() value (i.e. always <1 day) so =TIME(25,0,0) is 0100 not 2500, so TODAY()+TIME(25,0,0) is the same as TODAY()+TIME(1,0,0)
Try this instead:
=NOW()+B3/24 + C3/24/60 + D3/24/3600

View solution in original post