Forum Discussion
Adding integers to Date&Timd
| 2 | 2/4/22 1pm | 2/4/22 3pm |
| 1 | 2/4/22 3pm | 2/4/22 4pm |
| 2 | 2/4/22 4pm | 2/4/22 6pm |
| 2 | 2/4/22 6pm | 2/4/22 8pm |
I would like to know is there a formula to add the integers in Col 1 to Col 2 get the result in Col 3 in the that format. That is the 1st step. Then once that result is attained, I want to take the result in Col 3 and use that date in Col 2 on the 2nd row to add col 1 to get the result in Col 3 and so on down the page. I hope I explained it clear enough. I am struggling to keep up in my work since I have 20 or 30 pages to do this at a time. Thx
7 Replies
Hi,
I had tried to use that formula before without any success.
2.00
2/4/22 1pm
2/4/22 3pm
1.00
2/4/22 3pm
2/4/22 4pm
2.00
2/4/22 4pm
2/4/22 6pm
2.00
2/4/22 1pm
#VALUE!
=
A1/24 + B1
A1 is Number format with 2 d decimal places
B1&C1 format is Date Format 3/4/12 1:30pm
And I do have the = sign in front of A1/24 + B1 in Cell
with the error message #Value!
So what am I missing here?
Thx
Rembrandt
- Just because you have the cell set to Date format (or Number format) does NOT mean the VALUE in that cell is a Date (or Number). You can prove this by typing any text string into one of those cells and you will see that text string that is clearly not a date. So most likely that "Date" 2/4/22 1pm is NOT recognized as a date. try re-typing that date in and I suspect it will change to a more 'standard' format like 2/4/22 1:00pm (if you specifically selected that format) or 2/4/2022 if you just select Short Date
- It is in the date format and just because it doesn't show the 2 zeroes with the time doesn't mean it is not in the date mode and here is why. When I type an 30 minutes in the time it does show up the format of 2/4/22 1:00pm but not until I actually put minutes in it.
Start Finish
1 1/31/22 6:am 2/2/22 7:am
2 1/31/22 6:am 2/2/22 9:am
Start Finish
1 1/31/22 6:30am 2/2/22 7:30am
2 1/31/22 6:30am 2/2/22 9:30am
- To add a # hours to a date you just need to divide by 24 and add:
=A1/24 + B1
then in B2 you can either use that same formula or use =C1- BTW you then just fill down. If you already have col A then if you hover over the bottom right corner of cell B2 you will get a large "+" icon and can likely just double-click and it will auto-fill down and then do the same for cell C1. If double-click doesn't work for some reason you can get that same large "+" and drag down or highlight the cell and use CTRL-D to fill down
