JTL9161
Well-known Member
- Joined
- Aug 29, 2012
- Messages
- 593
- Office Version
- 365
- Platform
- Windows
We have a file that runs a few times everyday (m-f). For the ones that run in the afternoon depending on the size of each file it could end before 4:00pm or after. The file puts a end timestamp. After importing to Excel I use this formula to extract the time from the file. =MID('NORXFILE'!A18,SEARCH("ct",'NORXFILE'!A18),5)
So with this I might get a time like 7:06. I thought excel would not see this as a time since it was just imported but when I do =A44 - TIME(2, 0, 0) I get 5:06am then I use =A44+0.5 giving me 5:05pm in cell A45.
Now my question. Is there a IF statement that will look at this the imported time (7:06) and determine if it is before or after 4:00pm? If its before 4:00 I just want it to show as is. If the time is greater than 4:00 but less than 6:00 subtract 3 hours. If the time is greater than 6:00 then subtract 4 hours. Again I do not think excel is treating the data as time even though the cell is formatted to show like time. We need the time to be before 4:00 to stay in the same business day (even though 7:06 is in the same day)
Appreciate you help
James
So with this I might get a time like 7:06. I thought excel would not see this as a time since it was just imported but when I do =A44 - TIME(2, 0, 0) I get 5:06am then I use =A44+0.5 giving me 5:05pm in cell A45.
Now my question. Is there a IF statement that will look at this the imported time (7:06) and determine if it is before or after 4:00pm? If its before 4:00 I just want it to show as is. If the time is greater than 4:00 but less than 6:00 subtract 3 hours. If the time is greater than 6:00 then subtract 4 hours. Again I do not think excel is treating the data as time even though the cell is formatted to show like time. We need the time to be before 4:00 to stay in the same business day (even though 7:06 is in the same day)
Appreciate you help
James