Log In
Register
Home
Active Member
June 2, 2021
Offline
Hello!
I'm working with an Excel table in which each row is a document produced five minutes after the previous document, with the first one starting at a different time each weekday. Unfortunately that bit's missing, so I'm trying to write an IF formula that assigns start times based on the document code and the weekday. I tried writing one for all combinations of code and weekday, but the codes run from AA to HD, so that formula was too long. Here's the easy bit I've done so far. Is it possible to reference the previous cell within the formula and add five minutes, or would I be able to do that in another column? Any help gratefully received!
if [Weekday] = "Mon" and [Document Code] = "AA" then "14:40"
else if [Weekday] = "Tue" and [Document Code] = "AA" then "11:40"
else if [Weekday] = "Wed" and [Document Code] = "AA" then "11:40"
else if [Weekday] = "Thu" and [Document Code] = "AA" then "09:40"
else if [Weekday] = "Fri" and [Document Code] = "AA" then "09:40"
else ??? + #duration (0,0,0,5)
Many thanks
Saul
July 16, 2010
Offline
Hi Saul,
Welcome to our forum! You could store the time criteria in another table and do the equivalent of a VLOOKUP approximate match in Power Query. If you get stuck, please upload a sample file so we can see the structure of your data and give you a solution.
Mynda
1 Guest(s)
