Solved

Display current day and time in google sheet

Posted on 2014-11-12
5
197 Views
Last Modified: 2014-11-13
Sheet:  https://docs.google.com/spreadsheets/d/1hpp_HYEOVPzBnfCRZwQfpsYuAxkK8e31OVeFzAz4tsg/edit#gid=323269223

The current formula on row 12 is this =IF(E12="","",IF(E12="+","+",text(now()+5/24+E12/24,"ddd hh:mm a")))
However, it appears wrong because it only produces an AM value instead of both.

I need to base the current day and time in cell F12 and the offset in the adjacent E12 cell using a formula.  Using the fill handle, I need the rest of the cells to fill with the current time based on their offsets.

Assistance in correcting this is greatly appreciated.
0
Comment
Question by:frugalmule
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40440462
HI,

pls try

=IF(E12=ʺʺ,ʺʺ,IF(E12=ʺ+ʺ,ʺ+ʺ,text(now()+5/24+E12/24,ʺddd hh:mm AM/PMʺ)))

Regards
0
 
LVL 7

Accepted Solution

by:
tomfarrar earned 250 total points
ID: 40440510
This seems to work to:
=IF(ISBLANK(D4),"",TEXT(NOW()+VALUE(D4/24),"ddd hh:mm am/pm"))
0
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 40440560
Pls try

Corrected formula

=IF(E12="","",TEXT(NOW()+5/24+VALUE(E12/24),"ddd hh:mm am/pm"))

Regards
0
 

Author Closing Comment

by:frugalmule
ID: 40441781
Thank you both very much.  I chose the first one only because it looked more understandable to me than the second one.  However, there may have been a good reason to choose the second one so I would like to open up for discussion.  Thanks very much for the contribution.

1. =IF(ISBLANK(D4),"",TEXT(NOW()+VALUE(D4/24),"ddd hh:mm am/pm"))

2. =IF(E12="","",TEXT(NOW()+5/24+VALUE(E12/24),"ddd hh:mm am/pm")).
0
 

Author Comment

by:frugalmule
ID: 40441808
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many add-ons for Google docs, slides and sheets that can be extremely helpful in keeping your files organized and simple to use. Add-ons are installed into your Google docs, slides or sheets through Google's Add-On Store. One of my favorit…
You can provide a virtual interface for remote stakeholders in a SWOT analysis through a Google Drawing template. By making real time viewing and collaboration possible, your team can build a stronger product.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question