Forum Discussion
Use displayed value in a cell rather than the input function, for use in another function?
Hello Excel community! This is my first time posting - and I do apologize if this is a repeat question.
First, a bit of background (though I will bold where my specific question starts)
Using power automate, I am able to set up an automated process combining a Form with a Spreadsheet, that captures (among other details) the timestamp of when the form was submitted.
Right now, PowerAutomate only supplies the timestamp in GMT, and I need it to be in PST. Using Convert-to-Text, I have managed to split the supplied timestamp into separate date and time columns, and I have been able to use CELL-TIME(8,0,0) to convert the respective values into to PST. So, in terms of the table, all the information is appropriately displayed.
My issue is, if I want to use an Index/Match combination to return specific information based off the adjusted timestamp (PST, as opposed to GMT), it does not seem possible. If I try to look up a particular PST date, it is unable to locate that information, because the actual contents of the cell is CELL-Time(8,0,0) instead of MM/DD/YYYY.
Is there a way for functions to use a displayed/"processed" value of a cell, rather than the exact contents?
Or, is there a way to change the output cell of a function, so that the "processed" value would be separate, and available for referencing in a secondary function?
So far I have tried to use an Index/Match combination where I do a "reverse conversion", where I take the date to look up, convert it to GMT, and then try to match that value, but it either is not possible, or I am entering it wrong. The formula I tried to write is below...
=index(ReturnColumn, match(LookUpDateCell+Time(8,0,0), LookUpDateColumn, 0),)
My knowledge of excel is fairly lightweight, but I like to think I can pick up on things pretty quickly. The function(s) I use most often are Index/Match, Sum, SumIf(s), Countif(s), and IfError (to keep things clean looking).
Thank you in advance for any advice or guidance!
4 Replies
Oh, such better to discuss with small sample file with only few records to illustrate an issue.
Do I understood correctly what in
match(LookUpDateCell+Time(8,0,0), LookUpDateColumn, 0)
you compare datetime lookup value with LookUpDateColumn which has only dates? If so, it could be
..match(INT(LookUpDateCell+Time(8,0,0)), LookUpDateColumn, 0)Or LookUpDateColumn column is also datetime and you try to find exact time for the date?
By the way, didn't check now but as I remember in Power Automate there is a function like ConvertTimeZone or so, you may do such conversions within it.
Hello again Sergei - your advice about the time zone conversion tool in Power Automate did the trick!
I no longer need to use excel to convert between everything - so now it all works smoothly!Thank you so much.
scupacoffee , you are welcome
Hello! Thank you for your message. I should have thought ahead to include a sample file - I've attached it to this message, and I will edit the original post to include it as well.I have created one row of sample data, as the real data itself may be subject to privacy protections.
The goal is to be able to use the PST date for reference purposes, and as of right now I am at a loss for how to do so. It also appears that the "fix" I thought I had is ineffective... so I will remove that from my original post, too.
In the mean time, I will examine PowerAutomate further to see if the timezone conversion tool is available! Thank you.
